1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from decimal import Decimal
  24from enum import auto
  25from functools import reduce
  26
  27from sqlglot.errors import ErrorLevel, ParseError
  28from sqlglot.helper import (
  29    AutoName,
  30    camel_to_snake_case,
  31    ensure_collection,
  32    ensure_list,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token, TokenError
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43    S = t.TypeVar("S", bound="SetOperation")
  44
  45
  46class _Expression(type):
  47    def __new__(cls, clsname, bases, attrs):
  48        klass = super().__new__(cls, clsname, bases, attrs)
  49
  50        # When an Expression class is created, its key is automatically set to be
  51        # the lowercase version of the class' name.
  52        klass.key = clsname.lower()
  53
  54        # This is so that docstrings are not inherited in pdoc
  55        klass.__doc__ = klass.__doc__ or ""
  56
  57        return klass
  58
  59
  60SQLGLOT_META = "sqlglot.meta"
  61TABLE_PARTS = ("this", "db", "catalog")
  62COLUMN_PARTS = ("this", "table", "db", "catalog")
  63
  64
  65class Expression(metaclass=_Expression):
  66    """
  67    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  68    context, such as its child expressions, their names (arg keys), and whether a given child expression
  69    is optional or not.
  70
  71    Attributes:
  72        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  73            and representing expressions as strings.
  74        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  75            arg keys to booleans that indicate whether the corresponding args are optional.
  76        parent: a reference to the parent expression (or None, in case of root expressions).
  77        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  78            uses to refer to it.
  79        index: the index of an expression if it is inside of a list argument in its parent.
  80        comments: a list of comments that are associated with a given expression. This is used in
  81            order to preserve comments when transpiling SQL code.
  82        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  83            optimizer, in order to enable some transformations that require type information.
  84        meta: a dictionary that can be used to store useful metadata for a given expression.
  85
  86    Example:
  87        >>> class Foo(Expression):
  88        ...     arg_types = {"this": True, "expression": False}
  89
  90        The above definition informs us that Foo is an Expression that requires an argument called
  91        "this" and may also optionally receive an argument called "expression".
  92
  93    Args:
  94        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  95    """
  96
  97    key = "expression"
  98    arg_types = {"this": True}
  99    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
 100
 101    def __init__(self, **args: t.Any):
 102        self.args: t.Dict[str, t.Any] = args
 103        self.parent: t.Optional[Expression] = None
 104        self.arg_key: t.Optional[str] = None
 105        self.index: t.Optional[int] = None
 106        self.comments: t.Optional[t.List[str]] = None
 107        self._type: t.Optional[DataType] = None
 108        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 109        self._hash: t.Optional[int] = None
 110
 111        for arg_key, value in self.args.items():
 112            self._set_parent(arg_key, value)
 113
 114    def __eq__(self, other) -> bool:
 115        return type(self) is type(other) and hash(self) == hash(other)
 116
 117    @property
 118    def hashable_args(self) -> t.Any:
 119        return frozenset(
 120            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 121            for k, v in self.args.items()
 122            if not (v is None or v is False or (type(v) is list and not v))
 123        )
 124
 125    def __hash__(self) -> int:
 126        if self._hash is not None:
 127            return self._hash
 128
 129        return hash((self.__class__, self.hashable_args))
 130
 131    @property
 132    def this(self) -> t.Any:
 133        """
 134        Retrieves the argument with key "this".
 135        """
 136        return self.args.get("this")
 137
 138    @property
 139    def expression(self) -> t.Any:
 140        """
 141        Retrieves the argument with key "expression".
 142        """
 143        return self.args.get("expression")
 144
 145    @property
 146    def expressions(self) -> t.List[t.Any]:
 147        """
 148        Retrieves the argument with key "expressions".
 149        """
 150        return self.args.get("expressions") or []
 151
 152    def text(self, key) -> str:
 153        """
 154        Returns a textual representation of the argument corresponding to "key". This can only be used
 155        for args that are strings or leaf Expression instances, such as identifiers and literals.
 156        """
 157        field = self.args.get(key)
 158        if isinstance(field, str):
 159            return field
 160        if isinstance(field, (Identifier, Literal, Var)):
 161            return field.this
 162        if isinstance(field, (Star, Null)):
 163            return field.name
 164        return ""
 165
 166    @property
 167    def is_string(self) -> bool:
 168        """
 169        Checks whether a Literal expression is a string.
 170        """
 171        return isinstance(self, Literal) and self.args["is_string"]
 172
 173    @property
 174    def is_number(self) -> bool:
 175        """
 176        Checks whether a Literal expression is a number.
 177        """
 178        return (isinstance(self, Literal) and not self.args["is_string"]) or (
 179            isinstance(self, Neg) and self.this.is_number
 180        )
 181
 182    def to_py(self) -> t.Any:
 183        """
 184        Returns a Python object equivalent of the SQL node.
 185        """
 186        raise ValueError(f"{self} cannot be converted to a Python object.")
 187
 188    @property
 189    def is_int(self) -> bool:
 190        """
 191        Checks whether an expression is an integer.
 192        """
 193        return self.is_number and isinstance(self.to_py(), int)
 194
 195    @property
 196    def is_star(self) -> bool:
 197        """Checks whether an expression is a star."""
 198        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 199
 200    @property
 201    def alias(self) -> str:
 202        """
 203        Returns the alias of the expression, or an empty string if it's not aliased.
 204        """
 205        if isinstance(self.args.get("alias"), TableAlias):
 206            return self.args["alias"].name
 207        return self.text("alias")
 208
 209    @property
 210    def alias_column_names(self) -> t.List[str]:
 211        table_alias = self.args.get("alias")
 212        if not table_alias:
 213            return []
 214        return [c.name for c in table_alias.args.get("columns") or []]
 215
 216    @property
 217    def name(self) -> str:
 218        return self.text("this")
 219
 220    @property
 221    def alias_or_name(self) -> str:
 222        return self.alias or self.name
 223
 224    @property
 225    def output_name(self) -> str:
 226        """
 227        Name of the output column if this expression is a selection.
 228
 229        If the Expression has no output name, an empty string is returned.
 230
 231        Example:
 232            >>> from sqlglot import parse_one
 233            >>> parse_one("SELECT a").expressions[0].output_name
 234            'a'
 235            >>> parse_one("SELECT b AS c").expressions[0].output_name
 236            'c'
 237            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 238            ''
 239        """
 240        return ""
 241
 242    @property
 243    def type(self) -> t.Optional[DataType]:
 244        return self._type
 245
 246    @type.setter
 247    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 248        if dtype and not isinstance(dtype, DataType):
 249            dtype = DataType.build(dtype)
 250        self._type = dtype  # type: ignore
 251
 252    def is_type(self, *dtypes) -> bool:
 253        return self.type is not None and self.type.is_type(*dtypes)
 254
 255    def is_leaf(self) -> bool:
 256        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 257
 258    @property
 259    def meta(self) -> t.Dict[str, t.Any]:
 260        if self._meta is None:
 261            self._meta = {}
 262        return self._meta
 263
 264    def __deepcopy__(self, memo):
 265        root = self.__class__()
 266        stack = [(self, root)]
 267
 268        while stack:
 269            node, copy = stack.pop()
 270
 271            if node.comments is not None:
 272                copy.comments = deepcopy(node.comments)
 273            if node._type is not None:
 274                copy._type = deepcopy(node._type)
 275            if node._meta is not None:
 276                copy._meta = deepcopy(node._meta)
 277            if node._hash is not None:
 278                copy._hash = node._hash
 279
 280            for k, vs in node.args.items():
 281                if hasattr(vs, "parent"):
 282                    stack.append((vs, vs.__class__()))
 283                    copy.set(k, stack[-1][-1])
 284                elif type(vs) is list:
 285                    copy.args[k] = []
 286
 287                    for v in vs:
 288                        if hasattr(v, "parent"):
 289                            stack.append((v, v.__class__()))
 290                            copy.append(k, stack[-1][-1])
 291                        else:
 292                            copy.append(k, v)
 293                else:
 294                    copy.args[k] = vs
 295
 296        return root
 297
 298    def copy(self):
 299        """
 300        Returns a deep copy of the expression.
 301        """
 302        return deepcopy(self)
 303
 304    def add_comments(self, comments: t.Optional[t.List[str]] = None) -> None:
 305        if self.comments is None:
 306            self.comments = []
 307
 308        if comments:
 309            for comment in comments:
 310                _, *meta = comment.split(SQLGLOT_META)
 311                if meta:
 312                    for kv in "".join(meta).split(","):
 313                        k, *v = kv.split("=")
 314                        value = v[0].strip() if v else True
 315                        self.meta[k.strip()] = value
 316                self.comments.append(comment)
 317
 318    def pop_comments(self) -> t.List[str]:
 319        comments = self.comments or []
 320        self.comments = None
 321        return comments
 322
 323    def append(self, arg_key: str, value: t.Any) -> None:
 324        """
 325        Appends value to arg_key if it's a list or sets it as a new list.
 326
 327        Args:
 328            arg_key (str): name of the list expression arg
 329            value (Any): value to append to the list
 330        """
 331        if type(self.args.get(arg_key)) is not list:
 332            self.args[arg_key] = []
 333        self._set_parent(arg_key, value)
 334        values = self.args[arg_key]
 335        if hasattr(value, "parent"):
 336            value.index = len(values)
 337        values.append(value)
 338
 339    def set(
 340        self,
 341        arg_key: str,
 342        value: t.Any,
 343        index: t.Optional[int] = None,
 344        overwrite: bool = True,
 345    ) -> None:
 346        """
 347        Sets arg_key to value.
 348
 349        Args:
 350            arg_key: name of the expression arg.
 351            value: value to set the arg to.
 352            index: if the arg is a list, this specifies what position to add the value in it.
 353            overwrite: assuming an index is given, this determines whether to overwrite the
 354                list entry instead of only inserting a new value (i.e., like list.insert).
 355        """
 356        if index is not None:
 357            expressions = self.args.get(arg_key) or []
 358
 359            if seq_get(expressions, index) is None:
 360                return
 361            if value is None:
 362                expressions.pop(index)
 363                for v in expressions[index:]:
 364                    v.index = v.index - 1
 365                return
 366
 367            if isinstance(value, list):
 368                expressions.pop(index)
 369                expressions[index:index] = value
 370            elif overwrite:
 371                expressions[index] = value
 372            else:
 373                expressions.insert(index, value)
 374
 375            value = expressions
 376        elif value is None:
 377            self.args.pop(arg_key, None)
 378            return
 379
 380        self.args[arg_key] = value
 381        self._set_parent(arg_key, value, index)
 382
 383    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 384        if hasattr(value, "parent"):
 385            value.parent = self
 386            value.arg_key = arg_key
 387            value.index = index
 388        elif type(value) is list:
 389            for index, v in enumerate(value):
 390                if hasattr(v, "parent"):
 391                    v.parent = self
 392                    v.arg_key = arg_key
 393                    v.index = index
 394
 395    @property
 396    def depth(self) -> int:
 397        """
 398        Returns the depth of this tree.
 399        """
 400        if self.parent:
 401            return self.parent.depth + 1
 402        return 0
 403
 404    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 405        """Yields the key and expression for all arguments, exploding list args."""
 406        # remove tuple when python 3.7 is deprecated
 407        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 408            if type(vs) is list:
 409                for v in reversed(vs) if reverse else vs:
 410                    if hasattr(v, "parent"):
 411                        yield v
 412            else:
 413                if hasattr(vs, "parent"):
 414                    yield vs
 415
 416    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 417        """
 418        Returns the first node in this tree which matches at least one of
 419        the specified types.
 420
 421        Args:
 422            expression_types: the expression type(s) to match.
 423            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 424
 425        Returns:
 426            The node which matches the criteria or None if no such node was found.
 427        """
 428        return next(self.find_all(*expression_types, bfs=bfs), None)
 429
 430    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 431        """
 432        Returns a generator object which visits all nodes in this tree and only
 433        yields those that match at least one of the specified expression types.
 434
 435        Args:
 436            expression_types: the expression type(s) to match.
 437            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 438
 439        Returns:
 440            The generator object.
 441        """
 442        for expression in self.walk(bfs=bfs):
 443            if isinstance(expression, expression_types):
 444                yield expression
 445
 446    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 447        """
 448        Returns a nearest parent matching expression_types.
 449
 450        Args:
 451            expression_types: the expression type(s) to match.
 452
 453        Returns:
 454            The parent node.
 455        """
 456        ancestor = self.parent
 457        while ancestor and not isinstance(ancestor, expression_types):
 458            ancestor = ancestor.parent
 459        return ancestor  # type: ignore
 460
 461    @property
 462    def parent_select(self) -> t.Optional[Select]:
 463        """
 464        Returns the parent select statement.
 465        """
 466        return self.find_ancestor(Select)
 467
 468    @property
 469    def same_parent(self) -> bool:
 470        """Returns if the parent is the same class as itself."""
 471        return type(self.parent) is self.__class__
 472
 473    def root(self) -> Expression:
 474        """
 475        Returns the root expression of this tree.
 476        """
 477        expression = self
 478        while expression.parent:
 479            expression = expression.parent
 480        return expression
 481
 482    def walk(
 483        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 484    ) -> t.Iterator[Expression]:
 485        """
 486        Returns a generator object which visits all nodes in this tree.
 487
 488        Args:
 489            bfs: if set to True the BFS traversal order will be applied,
 490                otherwise the DFS traversal will be used instead.
 491            prune: callable that returns True if the generator should stop traversing
 492                this branch of the tree.
 493
 494        Returns:
 495            the generator object.
 496        """
 497        if bfs:
 498            yield from self.bfs(prune=prune)
 499        else:
 500            yield from self.dfs(prune=prune)
 501
 502    def dfs(
 503        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 504    ) -> t.Iterator[Expression]:
 505        """
 506        Returns a generator object which visits all nodes in this tree in
 507        the DFS (Depth-first) order.
 508
 509        Returns:
 510            The generator object.
 511        """
 512        stack = [self]
 513
 514        while stack:
 515            node = stack.pop()
 516
 517            yield node
 518
 519            if prune and prune(node):
 520                continue
 521
 522            for v in node.iter_expressions(reverse=True):
 523                stack.append(v)
 524
 525    def bfs(
 526        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 527    ) -> t.Iterator[Expression]:
 528        """
 529        Returns a generator object which visits all nodes in this tree in
 530        the BFS (Breadth-first) order.
 531
 532        Returns:
 533            The generator object.
 534        """
 535        queue = deque([self])
 536
 537        while queue:
 538            node = queue.popleft()
 539
 540            yield node
 541
 542            if prune and prune(node):
 543                continue
 544
 545            for v in node.iter_expressions():
 546                queue.append(v)
 547
 548    def unnest(self):
 549        """
 550        Returns the first non parenthesis child or self.
 551        """
 552        expression = self
 553        while type(expression) is Paren:
 554            expression = expression.this
 555        return expression
 556
 557    def unalias(self):
 558        """
 559        Returns the inner expression if this is an Alias.
 560        """
 561        if isinstance(self, Alias):
 562            return self.this
 563        return self
 564
 565    def unnest_operands(self):
 566        """
 567        Returns unnested operands as a tuple.
 568        """
 569        return tuple(arg.unnest() for arg in self.iter_expressions())
 570
 571    def flatten(self, unnest=True):
 572        """
 573        Returns a generator which yields child nodes whose parents are the same class.
 574
 575        A AND B AND C -> [A, B, C]
 576        """
 577        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 578            if type(node) is not self.__class__:
 579                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 580
 581    def __str__(self) -> str:
 582        return self.sql()
 583
 584    def __repr__(self) -> str:
 585        return _to_s(self)
 586
 587    def to_s(self) -> str:
 588        """
 589        Same as __repr__, but includes additional information which can be useful
 590        for debugging, like empty or missing args and the AST nodes' object IDs.
 591        """
 592        return _to_s(self, verbose=True)
 593
 594    def sql(self, dialect: DialectType = None, **opts) -> str:
 595        """
 596        Returns SQL string representation of this tree.
 597
 598        Args:
 599            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 600            opts: other `sqlglot.generator.Generator` options.
 601
 602        Returns:
 603            The SQL string.
 604        """
 605        from sqlglot.dialects import Dialect
 606
 607        return Dialect.get_or_raise(dialect).generate(self, **opts)
 608
 609    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 610        """
 611        Visits all tree nodes (excluding already transformed ones)
 612        and applies the given transformation function to each node.
 613
 614        Args:
 615            fun: a function which takes a node as an argument and returns a
 616                new transformed node or the same node without modifications. If the function
 617                returns None, then the corresponding node will be removed from the syntax tree.
 618            copy: if set to True a new tree instance is constructed, otherwise the tree is
 619                modified in place.
 620
 621        Returns:
 622            The transformed tree.
 623        """
 624        root = None
 625        new_node = None
 626
 627        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 628            parent, arg_key, index = node.parent, node.arg_key, node.index
 629            new_node = fun(node, *args, **kwargs)
 630
 631            if not root:
 632                root = new_node
 633            elif new_node is not node:
 634                parent.set(arg_key, new_node, index)
 635
 636        assert root
 637        return root.assert_is(Expression)
 638
 639    @t.overload
 640    def replace(self, expression: E) -> E: ...
 641
 642    @t.overload
 643    def replace(self, expression: None) -> None: ...
 644
 645    def replace(self, expression):
 646        """
 647        Swap out this expression with a new expression.
 648
 649        For example::
 650
 651            >>> tree = Select().select("x").from_("tbl")
 652            >>> tree.find(Column).replace(column("y"))
 653            Column(
 654              this=Identifier(this=y, quoted=False))
 655            >>> tree.sql()
 656            'SELECT y FROM tbl'
 657
 658        Args:
 659            expression: new node
 660
 661        Returns:
 662            The new expression or expressions.
 663        """
 664        parent = self.parent
 665
 666        if not parent or parent is expression:
 667            return expression
 668
 669        key = self.arg_key
 670        value = parent.args.get(key)
 671
 672        if type(expression) is list and isinstance(value, Expression):
 673            # We are trying to replace an Expression with a list, so it's assumed that
 674            # the intention was to really replace the parent of this expression.
 675            value.parent.replace(expression)
 676        else:
 677            parent.set(key, expression, self.index)
 678
 679        if expression is not self:
 680            self.parent = None
 681            self.arg_key = None
 682            self.index = None
 683
 684        return expression
 685
 686    def pop(self: E) -> E:
 687        """
 688        Remove this expression from its AST.
 689
 690        Returns:
 691            The popped expression.
 692        """
 693        self.replace(None)
 694        return self
 695
 696    def assert_is(self, type_: t.Type[E]) -> E:
 697        """
 698        Assert that this `Expression` is an instance of `type_`.
 699
 700        If it is NOT an instance of `type_`, this raises an assertion error.
 701        Otherwise, this returns this expression.
 702
 703        Examples:
 704            This is useful for type security in chained expressions:
 705
 706            >>> import sqlglot
 707            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 708            'SELECT x, z FROM y'
 709        """
 710        if not isinstance(self, type_):
 711            raise AssertionError(f"{self} is not {type_}.")
 712        return self
 713
 714    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 715        """
 716        Checks if this expression is valid (e.g. all mandatory args are set).
 717
 718        Args:
 719            args: a sequence of values that were used to instantiate a Func expression. This is used
 720                to check that the provided arguments don't exceed the function argument limit.
 721
 722        Returns:
 723            A list of error messages for all possible errors that were found.
 724        """
 725        errors: t.List[str] = []
 726
 727        for k in self.args:
 728            if k not in self.arg_types:
 729                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 730        for k, mandatory in self.arg_types.items():
 731            v = self.args.get(k)
 732            if mandatory and (v is None or (isinstance(v, list) and not v)):
 733                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 734
 735        if (
 736            args
 737            and isinstance(self, Func)
 738            and len(args) > len(self.arg_types)
 739            and not self.is_var_len_args
 740        ):
 741            errors.append(
 742                f"The number of provided arguments ({len(args)}) is greater than "
 743                f"the maximum number of supported arguments ({len(self.arg_types)})"
 744            )
 745
 746        return errors
 747
 748    def dump(self):
 749        """
 750        Dump this Expression to a JSON-serializable dict.
 751        """
 752        from sqlglot.serde import dump
 753
 754        return dump(self)
 755
 756    @classmethod
 757    def load(cls, obj):
 758        """
 759        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 760        """
 761        from sqlglot.serde import load
 762
 763        return load(obj)
 764
 765    def and_(
 766        self,
 767        *expressions: t.Optional[ExpOrStr],
 768        dialect: DialectType = None,
 769        copy: bool = True,
 770        **opts,
 771    ) -> Condition:
 772        """
 773        AND this condition with one or multiple expressions.
 774
 775        Example:
 776            >>> condition("x=1").and_("y=1").sql()
 777            'x = 1 AND y = 1'
 778
 779        Args:
 780            *expressions: the SQL code strings to parse.
 781                If an `Expression` instance is passed, it will be used as-is.
 782            dialect: the dialect used to parse the input expression.
 783            copy: whether to copy the involved expressions (only applies to Expressions).
 784            opts: other options to use to parse the input expressions.
 785
 786        Returns:
 787            The new And condition.
 788        """
 789        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 790
 791    def or_(
 792        self,
 793        *expressions: t.Optional[ExpOrStr],
 794        dialect: DialectType = None,
 795        copy: bool = True,
 796        **opts,
 797    ) -> Condition:
 798        """
 799        OR this condition with one or multiple expressions.
 800
 801        Example:
 802            >>> condition("x=1").or_("y=1").sql()
 803            'x = 1 OR y = 1'
 804
 805        Args:
 806            *expressions: the SQL code strings to parse.
 807                If an `Expression` instance is passed, it will be used as-is.
 808            dialect: the dialect used to parse the input expression.
 809            copy: whether to copy the involved expressions (only applies to Expressions).
 810            opts: other options to use to parse the input expressions.
 811
 812        Returns:
 813            The new Or condition.
 814        """
 815        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 816
 817    def not_(self, copy: bool = True):
 818        """
 819        Wrap this condition with NOT.
 820
 821        Example:
 822            >>> condition("x=1").not_().sql()
 823            'NOT x = 1'
 824
 825        Args:
 826            copy: whether to copy this object.
 827
 828        Returns:
 829            The new Not instance.
 830        """
 831        return not_(self, copy=copy)
 832
 833    def as_(
 834        self,
 835        alias: str | Identifier,
 836        quoted: t.Optional[bool] = None,
 837        dialect: DialectType = None,
 838        copy: bool = True,
 839        **opts,
 840    ) -> Alias:
 841        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 842
 843    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 844        this = self.copy()
 845        other = convert(other, copy=True)
 846        if not isinstance(this, klass) and not isinstance(other, klass):
 847            this = _wrap(this, Binary)
 848            other = _wrap(other, Binary)
 849        if reverse:
 850            return klass(this=other, expression=this)
 851        return klass(this=this, expression=other)
 852
 853    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 854        return Bracket(
 855            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 856        )
 857
 858    def __iter__(self) -> t.Iterator:
 859        if "expressions" in self.arg_types:
 860            return iter(self.args.get("expressions") or [])
 861        # We define this because __getitem__ converts Expression into an iterable, which is
 862        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 863        # See: https://peps.python.org/pep-0234/
 864        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 865
 866    def isin(
 867        self,
 868        *expressions: t.Any,
 869        query: t.Optional[ExpOrStr] = None,
 870        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 871        copy: bool = True,
 872        **opts,
 873    ) -> In:
 874        subquery = maybe_parse(query, copy=copy, **opts) if query else None
 875        if subquery and not isinstance(subquery, Subquery):
 876            subquery = subquery.subquery(copy=False)
 877
 878        return In(
 879            this=maybe_copy(self, copy),
 880            expressions=[convert(e, copy=copy) for e in expressions],
 881            query=subquery,
 882            unnest=(
 883                Unnest(
 884                    expressions=[
 885                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 886                        for e in ensure_list(unnest)
 887                    ]
 888                )
 889                if unnest
 890                else None
 891            ),
 892        )
 893
 894    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 895        return Between(
 896            this=maybe_copy(self, copy),
 897            low=convert(low, copy=copy, **opts),
 898            high=convert(high, copy=copy, **opts),
 899        )
 900
 901    def is_(self, other: ExpOrStr) -> Is:
 902        return self._binop(Is, other)
 903
 904    def like(self, other: ExpOrStr) -> Like:
 905        return self._binop(Like, other)
 906
 907    def ilike(self, other: ExpOrStr) -> ILike:
 908        return self._binop(ILike, other)
 909
 910    def eq(self, other: t.Any) -> EQ:
 911        return self._binop(EQ, other)
 912
 913    def neq(self, other: t.Any) -> NEQ:
 914        return self._binop(NEQ, other)
 915
 916    def rlike(self, other: ExpOrStr) -> RegexpLike:
 917        return self._binop(RegexpLike, other)
 918
 919    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 920        div = self._binop(Div, other)
 921        div.args["typed"] = typed
 922        div.args["safe"] = safe
 923        return div
 924
 925    def asc(self, nulls_first: bool = True) -> Ordered:
 926        return Ordered(this=self.copy(), nulls_first=nulls_first)
 927
 928    def desc(self, nulls_first: bool = False) -> Ordered:
 929        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 930
 931    def __lt__(self, other: t.Any) -> LT:
 932        return self._binop(LT, other)
 933
 934    def __le__(self, other: t.Any) -> LTE:
 935        return self._binop(LTE, other)
 936
 937    def __gt__(self, other: t.Any) -> GT:
 938        return self._binop(GT, other)
 939
 940    def __ge__(self, other: t.Any) -> GTE:
 941        return self._binop(GTE, other)
 942
 943    def __add__(self, other: t.Any) -> Add:
 944        return self._binop(Add, other)
 945
 946    def __radd__(self, other: t.Any) -> Add:
 947        return self._binop(Add, other, reverse=True)
 948
 949    def __sub__(self, other: t.Any) -> Sub:
 950        return self._binop(Sub, other)
 951
 952    def __rsub__(self, other: t.Any) -> Sub:
 953        return self._binop(Sub, other, reverse=True)
 954
 955    def __mul__(self, other: t.Any) -> Mul:
 956        return self._binop(Mul, other)
 957
 958    def __rmul__(self, other: t.Any) -> Mul:
 959        return self._binop(Mul, other, reverse=True)
 960
 961    def __truediv__(self, other: t.Any) -> Div:
 962        return self._binop(Div, other)
 963
 964    def __rtruediv__(self, other: t.Any) -> Div:
 965        return self._binop(Div, other, reverse=True)
 966
 967    def __floordiv__(self, other: t.Any) -> IntDiv:
 968        return self._binop(IntDiv, other)
 969
 970    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 971        return self._binop(IntDiv, other, reverse=True)
 972
 973    def __mod__(self, other: t.Any) -> Mod:
 974        return self._binop(Mod, other)
 975
 976    def __rmod__(self, other: t.Any) -> Mod:
 977        return self._binop(Mod, other, reverse=True)
 978
 979    def __pow__(self, other: t.Any) -> Pow:
 980        return self._binop(Pow, other)
 981
 982    def __rpow__(self, other: t.Any) -> Pow:
 983        return self._binop(Pow, other, reverse=True)
 984
 985    def __and__(self, other: t.Any) -> And:
 986        return self._binop(And, other)
 987
 988    def __rand__(self, other: t.Any) -> And:
 989        return self._binop(And, other, reverse=True)
 990
 991    def __or__(self, other: t.Any) -> Or:
 992        return self._binop(Or, other)
 993
 994    def __ror__(self, other: t.Any) -> Or:
 995        return self._binop(Or, other, reverse=True)
 996
 997    def __neg__(self) -> Neg:
 998        return Neg(this=_wrap(self.copy(), Binary))
 999
1000    def __invert__(self) -> Not:
1001        return not_(self.copy())
1002
1003
1004IntoType = t.Union[
1005    str,
1006    t.Type[Expression],
1007    t.Collection[t.Union[str, t.Type[Expression]]],
1008]
1009ExpOrStr = t.Union[str, Expression]
1010
1011
1012class Condition(Expression):
1013    """Logical conditions like x AND y, or simply x"""
1014
1015
1016class Predicate(Condition):
1017    """Relationships like x = y, x > 1, x >= y."""
1018
1019
1020class DerivedTable(Expression):
1021    @property
1022    def selects(self) -> t.List[Expression]:
1023        return self.this.selects if isinstance(self.this, Query) else []
1024
1025    @property
1026    def named_selects(self) -> t.List[str]:
1027        return [select.output_name for select in self.selects]
1028
1029
1030class Query(Expression):
1031    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1032        """
1033        Returns a `Subquery` that wraps around this query.
1034
1035        Example:
1036            >>> subquery = Select().select("x").from_("tbl").subquery()
1037            >>> Select().select("x").from_(subquery).sql()
1038            'SELECT x FROM (SELECT x FROM tbl)'
1039
1040        Args:
1041            alias: an optional alias for the subquery.
1042            copy: if `False`, modify this expression instance in-place.
1043        """
1044        instance = maybe_copy(self, copy)
1045        if not isinstance(alias, Expression):
1046            alias = TableAlias(this=to_identifier(alias)) if alias else None
1047
1048        return Subquery(this=instance, alias=alias)
1049
1050    def limit(
1051        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1052    ) -> Q:
1053        """
1054        Adds a LIMIT clause to this query.
1055
1056        Example:
1057            >>> select("1").union(select("1")).limit(1).sql()
1058            'SELECT 1 UNION SELECT 1 LIMIT 1'
1059
1060        Args:
1061            expression: the SQL code string to parse.
1062                This can also be an integer.
1063                If a `Limit` instance is passed, it will be used as-is.
1064                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1065            dialect: the dialect used to parse the input expression.
1066            copy: if `False`, modify this expression instance in-place.
1067            opts: other options to use to parse the input expressions.
1068
1069        Returns:
1070            A limited Select expression.
1071        """
1072        return _apply_builder(
1073            expression=expression,
1074            instance=self,
1075            arg="limit",
1076            into=Limit,
1077            prefix="LIMIT",
1078            dialect=dialect,
1079            copy=copy,
1080            into_arg="expression",
1081            **opts,
1082        )
1083
1084    def offset(
1085        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1086    ) -> Q:
1087        """
1088        Set the OFFSET expression.
1089
1090        Example:
1091            >>> Select().from_("tbl").select("x").offset(10).sql()
1092            'SELECT x FROM tbl OFFSET 10'
1093
1094        Args:
1095            expression: the SQL code string to parse.
1096                This can also be an integer.
1097                If a `Offset` instance is passed, this is used as-is.
1098                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
1099            dialect: the dialect used to parse the input expression.
1100            copy: if `False`, modify this expression instance in-place.
1101            opts: other options to use to parse the input expressions.
1102
1103        Returns:
1104            The modified Select expression.
1105        """
1106        return _apply_builder(
1107            expression=expression,
1108            instance=self,
1109            arg="offset",
1110            into=Offset,
1111            prefix="OFFSET",
1112            dialect=dialect,
1113            copy=copy,
1114            into_arg="expression",
1115            **opts,
1116        )
1117
1118    def order_by(
1119        self: Q,
1120        *expressions: t.Optional[ExpOrStr],
1121        append: bool = True,
1122        dialect: DialectType = None,
1123        copy: bool = True,
1124        **opts,
1125    ) -> Q:
1126        """
1127        Set the ORDER BY expression.
1128
1129        Example:
1130            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1131            'SELECT x FROM tbl ORDER BY x DESC'
1132
1133        Args:
1134            *expressions: the SQL code strings to parse.
1135                If a `Group` instance is passed, this is used as-is.
1136                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1137            append: if `True`, add to any existing expressions.
1138                Otherwise, this flattens all the `Order` expression into a single expression.
1139            dialect: the dialect used to parse the input expression.
1140            copy: if `False`, modify this expression instance in-place.
1141            opts: other options to use to parse the input expressions.
1142
1143        Returns:
1144            The modified Select expression.
1145        """
1146        return _apply_child_list_builder(
1147            *expressions,
1148            instance=self,
1149            arg="order",
1150            append=append,
1151            copy=copy,
1152            prefix="ORDER BY",
1153            into=Order,
1154            dialect=dialect,
1155            **opts,
1156        )
1157
1158    @property
1159    def ctes(self) -> t.List[CTE]:
1160        """Returns a list of all the CTEs attached to this query."""
1161        with_ = self.args.get("with")
1162        return with_.expressions if with_ else []
1163
1164    @property
1165    def selects(self) -> t.List[Expression]:
1166        """Returns the query's projections."""
1167        raise NotImplementedError("Query objects must implement `selects`")
1168
1169    @property
1170    def named_selects(self) -> t.List[str]:
1171        """Returns the output names of the query's projections."""
1172        raise NotImplementedError("Query objects must implement `named_selects`")
1173
1174    def select(
1175        self: Q,
1176        *expressions: t.Optional[ExpOrStr],
1177        append: bool = True,
1178        dialect: DialectType = None,
1179        copy: bool = True,
1180        **opts,
1181    ) -> Q:
1182        """
1183        Append to or set the SELECT expressions.
1184
1185        Example:
1186            >>> Select().select("x", "y").sql()
1187            'SELECT x, y'
1188
1189        Args:
1190            *expressions: the SQL code strings to parse.
1191                If an `Expression` instance is passed, it will be used as-is.
1192            append: if `True`, add to any existing expressions.
1193                Otherwise, this resets the expressions.
1194            dialect: the dialect used to parse the input expressions.
1195            copy: if `False`, modify this expression instance in-place.
1196            opts: other options to use to parse the input expressions.
1197
1198        Returns:
1199            The modified Query expression.
1200        """
1201        raise NotImplementedError("Query objects must implement `select`")
1202
1203    def with_(
1204        self: Q,
1205        alias: ExpOrStr,
1206        as_: ExpOrStr,
1207        recursive: t.Optional[bool] = None,
1208        materialized: t.Optional[bool] = None,
1209        append: bool = True,
1210        dialect: DialectType = None,
1211        copy: bool = True,
1212        **opts,
1213    ) -> Q:
1214        """
1215        Append to or set the common table expressions.
1216
1217        Example:
1218            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1219            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1220
1221        Args:
1222            alias: the SQL code string to parse as the table name.
1223                If an `Expression` instance is passed, this is used as-is.
1224            as_: the SQL code string to parse as the table expression.
1225                If an `Expression` instance is passed, it will be used as-is.
1226            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1227            materialized: set the MATERIALIZED part of the expression.
1228            append: if `True`, add to any existing expressions.
1229                Otherwise, this resets the expressions.
1230            dialect: the dialect used to parse the input expression.
1231            copy: if `False`, modify this expression instance in-place.
1232            opts: other options to use to parse the input expressions.
1233
1234        Returns:
1235            The modified expression.
1236        """
1237        return _apply_cte_builder(
1238            self,
1239            alias,
1240            as_,
1241            recursive=recursive,
1242            materialized=materialized,
1243            append=append,
1244            dialect=dialect,
1245            copy=copy,
1246            **opts,
1247        )
1248
1249    def union(
1250        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1251    ) -> Union:
1252        """
1253        Builds a UNION expression.
1254
1255        Example:
1256            >>> import sqlglot
1257            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1258            'SELECT * FROM foo UNION SELECT * FROM bla'
1259
1260        Args:
1261            expression: the SQL code string.
1262                If an `Expression` instance is passed, it will be used as-is.
1263            distinct: set the DISTINCT flag if and only if this is true.
1264            dialect: the dialect used to parse the input expression.
1265            opts: other options to use to parse the input expressions.
1266
1267        Returns:
1268            The new Union expression.
1269        """
1270        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1271
1272    def intersect(
1273        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1274    ) -> Intersect:
1275        """
1276        Builds an INTERSECT expression.
1277
1278        Example:
1279            >>> import sqlglot
1280            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1281            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1282
1283        Args:
1284            expression: the SQL code string.
1285                If an `Expression` instance is passed, it will be used as-is.
1286            distinct: set the DISTINCT flag if and only if this is true.
1287            dialect: the dialect used to parse the input expression.
1288            opts: other options to use to parse the input expressions.
1289
1290        Returns:
1291            The new Intersect expression.
1292        """
1293        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1294
1295    def except_(
1296        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1297    ) -> Except:
1298        """
1299        Builds an EXCEPT expression.
1300
1301        Example:
1302            >>> import sqlglot
1303            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1304            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1305
1306        Args:
1307            expression: the SQL code string.
1308                If an `Expression` instance is passed, it will be used as-is.
1309            distinct: set the DISTINCT flag if and only if this is true.
1310            dialect: the dialect used to parse the input expression.
1311            opts: other options to use to parse the input expressions.
1312
1313        Returns:
1314            The new Except expression.
1315        """
1316        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1317
1318
1319class UDTF(DerivedTable):
1320    @property
1321    def selects(self) -> t.List[Expression]:
1322        alias = self.args.get("alias")
1323        return alias.columns if alias else []
1324
1325
1326class Cache(Expression):
1327    arg_types = {
1328        "this": True,
1329        "lazy": False,
1330        "options": False,
1331        "expression": False,
1332    }
1333
1334
1335class Uncache(Expression):
1336    arg_types = {"this": True, "exists": False}
1337
1338
1339class Refresh(Expression):
1340    pass
1341
1342
1343class DDL(Expression):
1344    @property
1345    def ctes(self) -> t.List[CTE]:
1346        """Returns a list of all the CTEs attached to this statement."""
1347        with_ = self.args.get("with")
1348        return with_.expressions if with_ else []
1349
1350    @property
1351    def selects(self) -> t.List[Expression]:
1352        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1353        return self.expression.selects if isinstance(self.expression, Query) else []
1354
1355    @property
1356    def named_selects(self) -> t.List[str]:
1357        """
1358        If this statement contains a query (e.g. a CTAS), this returns the output
1359        names of the query's projections.
1360        """
1361        return self.expression.named_selects if isinstance(self.expression, Query) else []
1362
1363
1364class DML(Expression):
1365    def returning(
1366        self,
1367        expression: ExpOrStr,
1368        dialect: DialectType = None,
1369        copy: bool = True,
1370        **opts,
1371    ) -> DML:
1372        """
1373        Set the RETURNING expression. Not supported by all dialects.
1374
1375        Example:
1376            >>> delete("tbl").returning("*", dialect="postgres").sql()
1377            'DELETE FROM tbl RETURNING *'
1378
1379        Args:
1380            expression: the SQL code strings to parse.
1381                If an `Expression` instance is passed, it will be used as-is.
1382            dialect: the dialect used to parse the input expressions.
1383            copy: if `False`, modify this expression instance in-place.
1384            opts: other options to use to parse the input expressions.
1385
1386        Returns:
1387            Delete: the modified expression.
1388        """
1389        return _apply_builder(
1390            expression=expression,
1391            instance=self,
1392            arg="returning",
1393            prefix="RETURNING",
1394            dialect=dialect,
1395            copy=copy,
1396            into=Returning,
1397            **opts,
1398        )
1399
1400
1401class Create(DDL):
1402    arg_types = {
1403        "with": False,
1404        "this": True,
1405        "kind": True,
1406        "expression": False,
1407        "exists": False,
1408        "properties": False,
1409        "replace": False,
1410        "refresh": False,
1411        "unique": False,
1412        "indexes": False,
1413        "no_schema_binding": False,
1414        "begin": False,
1415        "end": False,
1416        "clone": False,
1417        "concurrently": False,
1418        "clustered": False,
1419    }
1420
1421    @property
1422    def kind(self) -> t.Optional[str]:
1423        kind = self.args.get("kind")
1424        return kind and kind.upper()
1425
1426
1427class SequenceProperties(Expression):
1428    arg_types = {
1429        "increment": False,
1430        "minvalue": False,
1431        "maxvalue": False,
1432        "cache": False,
1433        "start": False,
1434        "owned": False,
1435        "options": False,
1436    }
1437
1438
1439class TruncateTable(Expression):
1440    arg_types = {
1441        "expressions": True,
1442        "is_database": False,
1443        "exists": False,
1444        "only": False,
1445        "cluster": False,
1446        "identity": False,
1447        "option": False,
1448        "partition": False,
1449    }
1450
1451
1452# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1453# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1454# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1455class Clone(Expression):
1456    arg_types = {"this": True, "shallow": False, "copy": False}
1457
1458
1459class Describe(Expression):
1460    arg_types = {
1461        "this": True,
1462        "style": False,
1463        "kind": False,
1464        "expressions": False,
1465        "partition": False,
1466    }
1467
1468
1469# https://duckdb.org/docs/guides/meta/summarize.html
1470class Summarize(Expression):
1471    arg_types = {"this": True, "table": False}
1472
1473
1474class Kill(Expression):
1475    arg_types = {"this": True, "kind": False}
1476
1477
1478class Pragma(Expression):
1479    pass
1480
1481
1482class Declare(Expression):
1483    arg_types = {"expressions": True}
1484
1485
1486class DeclareItem(Expression):
1487    arg_types = {"this": True, "kind": True, "default": False}
1488
1489
1490class Set(Expression):
1491    arg_types = {"expressions": False, "unset": False, "tag": False}
1492
1493
1494class Heredoc(Expression):
1495    arg_types = {"this": True, "tag": False}
1496
1497
1498class SetItem(Expression):
1499    arg_types = {
1500        "this": False,
1501        "expressions": False,
1502        "kind": False,
1503        "collate": False,  # MySQL SET NAMES statement
1504        "global": False,
1505    }
1506
1507
1508class Show(Expression):
1509    arg_types = {
1510        "this": True,
1511        "history": False,
1512        "terse": False,
1513        "target": False,
1514        "offset": False,
1515        "starts_with": False,
1516        "limit": False,
1517        "from": False,
1518        "like": False,
1519        "where": False,
1520        "db": False,
1521        "scope": False,
1522        "scope_kind": False,
1523        "full": False,
1524        "mutex": False,
1525        "query": False,
1526        "channel": False,
1527        "global": False,
1528        "log": False,
1529        "position": False,
1530        "types": False,
1531    }
1532
1533
1534class UserDefinedFunction(Expression):
1535    arg_types = {"this": True, "expressions": False, "wrapped": False}
1536
1537
1538class CharacterSet(Expression):
1539    arg_types = {"this": True, "default": False}
1540
1541
1542class With(Expression):
1543    arg_types = {"expressions": True, "recursive": False}
1544
1545    @property
1546    def recursive(self) -> bool:
1547        return bool(self.args.get("recursive"))
1548
1549
1550class WithinGroup(Expression):
1551    arg_types = {"this": True, "expression": False}
1552
1553
1554# clickhouse supports scalar ctes
1555# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1556class CTE(DerivedTable):
1557    arg_types = {
1558        "this": True,
1559        "alias": True,
1560        "scalar": False,
1561        "materialized": False,
1562    }
1563
1564
1565class ProjectionDef(Expression):
1566    arg_types = {"this": True, "expression": True}
1567
1568
1569class TableAlias(Expression):
1570    arg_types = {"this": False, "columns": False}
1571
1572    @property
1573    def columns(self):
1574        return self.args.get("columns") or []
1575
1576
1577class BitString(Condition):
1578    pass
1579
1580
1581class HexString(Condition):
1582    pass
1583
1584
1585class ByteString(Condition):
1586    pass
1587
1588
1589class RawString(Condition):
1590    pass
1591
1592
1593class UnicodeString(Condition):
1594    arg_types = {"this": True, "escape": False}
1595
1596
1597class Column(Condition):
1598    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1599
1600    @property
1601    def table(self) -> str:
1602        return self.text("table")
1603
1604    @property
1605    def db(self) -> str:
1606        return self.text("db")
1607
1608    @property
1609    def catalog(self) -> str:
1610        return self.text("catalog")
1611
1612    @property
1613    def output_name(self) -> str:
1614        return self.name
1615
1616    @property
1617    def parts(self) -> t.List[Identifier]:
1618        """Return the parts of a column in order catalog, db, table, name."""
1619        return [
1620            t.cast(Identifier, self.args[part])
1621            for part in ("catalog", "db", "table", "this")
1622            if self.args.get(part)
1623        ]
1624
1625    def to_dot(self) -> Dot | Identifier:
1626        """Converts the column into a dot expression."""
1627        parts = self.parts
1628        parent = self.parent
1629
1630        while parent:
1631            if isinstance(parent, Dot):
1632                parts.append(parent.expression)
1633            parent = parent.parent
1634
1635        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1636
1637
1638class ColumnPosition(Expression):
1639    arg_types = {"this": False, "position": True}
1640
1641
1642class ColumnDef(Expression):
1643    arg_types = {
1644        "this": True,
1645        "kind": False,
1646        "constraints": False,
1647        "exists": False,
1648        "position": False,
1649    }
1650
1651    @property
1652    def constraints(self) -> t.List[ColumnConstraint]:
1653        return self.args.get("constraints") or []
1654
1655    @property
1656    def kind(self) -> t.Optional[DataType]:
1657        return self.args.get("kind")
1658
1659
1660class AlterColumn(Expression):
1661    arg_types = {
1662        "this": True,
1663        "dtype": False,
1664        "collate": False,
1665        "using": False,
1666        "default": False,
1667        "drop": False,
1668        "comment": False,
1669        "allow_null": False,
1670    }
1671
1672
1673# https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html
1674class AlterDistStyle(Expression):
1675    pass
1676
1677
1678class AlterSortKey(Expression):
1679    arg_types = {"this": False, "expressions": False, "compound": False}
1680
1681
1682class AlterSet(Expression):
1683    arg_types = {
1684        "expressions": False,
1685        "option": False,
1686        "tablespace": False,
1687        "access_method": False,
1688        "file_format": False,
1689        "copy_options": False,
1690        "tag": False,
1691        "location": False,
1692        "serde": False,
1693    }
1694
1695
1696class RenameColumn(Expression):
1697    arg_types = {"this": True, "to": True, "exists": False}
1698
1699
1700class RenameTable(Expression):
1701    pass
1702
1703
1704class SwapTable(Expression):
1705    pass
1706
1707
1708class Comment(Expression):
1709    arg_types = {
1710        "this": True,
1711        "kind": True,
1712        "expression": True,
1713        "exists": False,
1714        "materialized": False,
1715    }
1716
1717
1718class Comprehension(Expression):
1719    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1720
1721
1722# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1723class MergeTreeTTLAction(Expression):
1724    arg_types = {
1725        "this": True,
1726        "delete": False,
1727        "recompress": False,
1728        "to_disk": False,
1729        "to_volume": False,
1730    }
1731
1732
1733# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1734class MergeTreeTTL(Expression):
1735    arg_types = {
1736        "expressions": True,
1737        "where": False,
1738        "group": False,
1739        "aggregates": False,
1740    }
1741
1742
1743# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1744class IndexConstraintOption(Expression):
1745    arg_types = {
1746        "key_block_size": False,
1747        "using": False,
1748        "parser": False,
1749        "comment": False,
1750        "visible": False,
1751        "engine_attr": False,
1752        "secondary_engine_attr": False,
1753    }
1754
1755
1756class ColumnConstraint(Expression):
1757    arg_types = {"this": False, "kind": True}
1758
1759    @property
1760    def kind(self) -> ColumnConstraintKind:
1761        return self.args["kind"]
1762
1763
1764class ColumnConstraintKind(Expression):
1765    pass
1766
1767
1768class AutoIncrementColumnConstraint(ColumnConstraintKind):
1769    pass
1770
1771
1772class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1773    arg_types = {"this": True, "expression": True}
1774
1775
1776class CaseSpecificColumnConstraint(ColumnConstraintKind):
1777    arg_types = {"not_": True}
1778
1779
1780class CharacterSetColumnConstraint(ColumnConstraintKind):
1781    arg_types = {"this": True}
1782
1783
1784class CheckColumnConstraint(ColumnConstraintKind):
1785    arg_types = {"this": True, "enforced": False}
1786
1787
1788class ClusteredColumnConstraint(ColumnConstraintKind):
1789    pass
1790
1791
1792class CollateColumnConstraint(ColumnConstraintKind):
1793    pass
1794
1795
1796class CommentColumnConstraint(ColumnConstraintKind):
1797    pass
1798
1799
1800class CompressColumnConstraint(ColumnConstraintKind):
1801    arg_types = {"this": False}
1802
1803
1804class DateFormatColumnConstraint(ColumnConstraintKind):
1805    arg_types = {"this": True}
1806
1807
1808class DefaultColumnConstraint(ColumnConstraintKind):
1809    pass
1810
1811
1812class EncodeColumnConstraint(ColumnConstraintKind):
1813    pass
1814
1815
1816# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1817class ExcludeColumnConstraint(ColumnConstraintKind):
1818    pass
1819
1820
1821class EphemeralColumnConstraint(ColumnConstraintKind):
1822    arg_types = {"this": False}
1823
1824
1825class WithOperator(Expression):
1826    arg_types = {"this": True, "op": True}
1827
1828
1829class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1830    # this: True -> ALWAYS, this: False -> BY DEFAULT
1831    arg_types = {
1832        "this": False,
1833        "expression": False,
1834        "on_null": False,
1835        "start": False,
1836        "increment": False,
1837        "minvalue": False,
1838        "maxvalue": False,
1839        "cycle": False,
1840    }
1841
1842
1843class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1844    arg_types = {"start": False, "hidden": False}
1845
1846
1847# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1848# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
1849class IndexColumnConstraint(ColumnConstraintKind):
1850    arg_types = {
1851        "this": False,
1852        "expressions": False,
1853        "kind": False,
1854        "index_type": False,
1855        "options": False,
1856        "expression": False,  # Clickhouse
1857        "granularity": False,
1858    }
1859
1860
1861class InlineLengthColumnConstraint(ColumnConstraintKind):
1862    pass
1863
1864
1865class NonClusteredColumnConstraint(ColumnConstraintKind):
1866    pass
1867
1868
1869class NotForReplicationColumnConstraint(ColumnConstraintKind):
1870    arg_types = {}
1871
1872
1873# https://docs.snowflake.com/en/sql-reference/sql/create-table
1874class MaskingPolicyColumnConstraint(ColumnConstraintKind):
1875    arg_types = {"this": True, "expressions": False}
1876
1877
1878class NotNullColumnConstraint(ColumnConstraintKind):
1879    arg_types = {"allow_null": False}
1880
1881
1882# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1883class OnUpdateColumnConstraint(ColumnConstraintKind):
1884    pass
1885
1886
1887# https://docs.snowflake.com/en/sql-reference/sql/create-table
1888class TagColumnConstraint(ColumnConstraintKind):
1889    arg_types = {"expressions": True}
1890
1891
1892# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1893class TransformColumnConstraint(ColumnConstraintKind):
1894    pass
1895
1896
1897class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1898    arg_types = {"desc": False}
1899
1900
1901class TitleColumnConstraint(ColumnConstraintKind):
1902    pass
1903
1904
1905class UniqueColumnConstraint(ColumnConstraintKind):
1906    arg_types = {"this": False, "index_type": False, "on_conflict": False, "nulls": False}
1907
1908
1909class UppercaseColumnConstraint(ColumnConstraintKind):
1910    arg_types: t.Dict[str, t.Any] = {}
1911
1912
1913class PathColumnConstraint(ColumnConstraintKind):
1914    pass
1915
1916
1917# https://docs.snowflake.com/en/sql-reference/sql/create-table
1918class ProjectionPolicyColumnConstraint(ColumnConstraintKind):
1919    pass
1920
1921
1922# computed column expression
1923# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1924class ComputedColumnConstraint(ColumnConstraintKind):
1925    arg_types = {"this": True, "persisted": False, "not_null": False}
1926
1927
1928class Constraint(Expression):
1929    arg_types = {"this": True, "expressions": True}
1930
1931
1932class Delete(DML):
1933    arg_types = {
1934        "with": False,
1935        "this": False,
1936        "using": False,
1937        "where": False,
1938        "returning": False,
1939        "limit": False,
1940        "tables": False,  # Multiple-Table Syntax (MySQL)
1941    }
1942
1943    def delete(
1944        self,
1945        table: ExpOrStr,
1946        dialect: DialectType = None,
1947        copy: bool = True,
1948        **opts,
1949    ) -> Delete:
1950        """
1951        Create a DELETE expression or replace the table on an existing DELETE expression.
1952
1953        Example:
1954            >>> delete("tbl").sql()
1955            'DELETE FROM tbl'
1956
1957        Args:
1958            table: the table from which to delete.
1959            dialect: the dialect used to parse the input expression.
1960            copy: if `False`, modify this expression instance in-place.
1961            opts: other options to use to parse the input expressions.
1962
1963        Returns:
1964            Delete: the modified expression.
1965        """
1966        return _apply_builder(
1967            expression=table,
1968            instance=self,
1969            arg="this",
1970            dialect=dialect,
1971            into=Table,
1972            copy=copy,
1973            **opts,
1974        )
1975
1976    def where(
1977        self,
1978        *expressions: t.Optional[ExpOrStr],
1979        append: bool = True,
1980        dialect: DialectType = None,
1981        copy: bool = True,
1982        **opts,
1983    ) -> Delete:
1984        """
1985        Append to or set the WHERE expressions.
1986
1987        Example:
1988            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1989            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1990
1991        Args:
1992            *expressions: the SQL code strings to parse.
1993                If an `Expression` instance is passed, it will be used as-is.
1994                Multiple expressions are combined with an AND operator.
1995            append: if `True`, AND the new expressions to any existing expression.
1996                Otherwise, this resets the expression.
1997            dialect: the dialect used to parse the input expressions.
1998            copy: if `False`, modify this expression instance in-place.
1999            opts: other options to use to parse the input expressions.
2000
2001        Returns:
2002            Delete: the modified expression.
2003        """
2004        return _apply_conjunction_builder(
2005            *expressions,
2006            instance=self,
2007            arg="where",
2008            append=append,
2009            into=Where,
2010            dialect=dialect,
2011            copy=copy,
2012            **opts,
2013        )
2014
2015
2016class Drop(Expression):
2017    arg_types = {
2018        "this": False,
2019        "kind": False,
2020        "expressions": False,
2021        "exists": False,
2022        "temporary": False,
2023        "materialized": False,
2024        "cascade": False,
2025        "constraints": False,
2026        "purge": False,
2027        "cluster": False,
2028        "concurrently": False,
2029    }
2030
2031    @property
2032    def kind(self) -> t.Optional[str]:
2033        kind = self.args.get("kind")
2034        return kind and kind.upper()
2035
2036
2037class Filter(Expression):
2038    arg_types = {"this": True, "expression": True}
2039
2040
2041class Check(Expression):
2042    pass
2043
2044
2045class Changes(Expression):
2046    arg_types = {"information": True, "at_before": False, "end": False}
2047
2048
2049# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
2050class Connect(Expression):
2051    arg_types = {"start": False, "connect": True, "nocycle": False}
2052
2053
2054class CopyParameter(Expression):
2055    arg_types = {"this": True, "expression": False, "expressions": False}
2056
2057
2058class Copy(DML):
2059    arg_types = {
2060        "this": True,
2061        "kind": True,
2062        "files": True,
2063        "credentials": False,
2064        "format": False,
2065        "params": False,
2066    }
2067
2068
2069class Credentials(Expression):
2070    arg_types = {
2071        "credentials": False,
2072        "encryption": False,
2073        "storage": False,
2074        "iam_role": False,
2075        "region": False,
2076    }
2077
2078
2079class Prior(Expression):
2080    pass
2081
2082
2083class Directory(Expression):
2084    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
2085    arg_types = {"this": True, "local": False, "row_format": False}
2086
2087
2088class ForeignKey(Expression):
2089    arg_types = {
2090        "expressions": True,
2091        "reference": False,
2092        "delete": False,
2093        "update": False,
2094    }
2095
2096
2097class ColumnPrefix(Expression):
2098    arg_types = {"this": True, "expression": True}
2099
2100
2101class PrimaryKey(Expression):
2102    arg_types = {"expressions": True, "options": False}
2103
2104
2105# https://www.postgresql.org/docs/9.1/sql-selectinto.html
2106# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
2107class Into(Expression):
2108    arg_types = {"this": True, "temporary": False, "unlogged": False}
2109
2110
2111class From(Expression):
2112    @property
2113    def name(self) -> str:
2114        return self.this.name
2115
2116    @property
2117    def alias_or_name(self) -> str:
2118        return self.this.alias_or_name
2119
2120
2121class Having(Expression):
2122    pass
2123
2124
2125class Hint(Expression):
2126    arg_types = {"expressions": True}
2127
2128
2129class JoinHint(Expression):
2130    arg_types = {"this": True, "expressions": True}
2131
2132
2133class Identifier(Expression):
2134    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
2135
2136    @property
2137    def quoted(self) -> bool:
2138        return bool(self.args.get("quoted"))
2139
2140    @property
2141    def hashable_args(self) -> t.Any:
2142        return (self.this, self.quoted)
2143
2144    @property
2145    def output_name(self) -> str:
2146        return self.name
2147
2148
2149# https://www.postgresql.org/docs/current/indexes-opclass.html
2150class Opclass(Expression):
2151    arg_types = {"this": True, "expression": True}
2152
2153
2154class Index(Expression):
2155    arg_types = {
2156        "this": False,
2157        "table": False,
2158        "unique": False,
2159        "primary": False,
2160        "amp": False,  # teradata
2161        "params": False,
2162    }
2163
2164
2165class IndexParameters(Expression):
2166    arg_types = {
2167        "using": False,
2168        "include": False,
2169        "columns": False,
2170        "with_storage": False,
2171        "partition_by": False,
2172        "tablespace": False,
2173        "where": False,
2174        "on": False,
2175    }
2176
2177
2178class Insert(DDL, DML):
2179    arg_types = {
2180        "hint": False,
2181        "with": False,
2182        "is_function": False,
2183        "this": False,
2184        "expression": False,
2185        "conflict": False,
2186        "returning": False,
2187        "overwrite": False,
2188        "exists": False,
2189        "alternative": False,
2190        "where": False,
2191        "ignore": False,
2192        "by_name": False,
2193        "stored": False,
2194        "partition": False,
2195        "settings": False,
2196        "source": False,
2197    }
2198
2199    def with_(
2200        self,
2201        alias: ExpOrStr,
2202        as_: ExpOrStr,
2203        recursive: t.Optional[bool] = None,
2204        materialized: t.Optional[bool] = None,
2205        append: bool = True,
2206        dialect: DialectType = None,
2207        copy: bool = True,
2208        **opts,
2209    ) -> Insert:
2210        """
2211        Append to or set the common table expressions.
2212
2213        Example:
2214            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
2215            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
2216
2217        Args:
2218            alias: the SQL code string to parse as the table name.
2219                If an `Expression` instance is passed, this is used as-is.
2220            as_: the SQL code string to parse as the table expression.
2221                If an `Expression` instance is passed, it will be used as-is.
2222            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2223            materialized: set the MATERIALIZED part of the expression.
2224            append: if `True`, add to any existing expressions.
2225                Otherwise, this resets the expressions.
2226            dialect: the dialect used to parse the input expression.
2227            copy: if `False`, modify this expression instance in-place.
2228            opts: other options to use to parse the input expressions.
2229
2230        Returns:
2231            The modified expression.
2232        """
2233        return _apply_cte_builder(
2234            self,
2235            alias,
2236            as_,
2237            recursive=recursive,
2238            materialized=materialized,
2239            append=append,
2240            dialect=dialect,
2241            copy=copy,
2242            **opts,
2243        )
2244
2245
2246class ConditionalInsert(Expression):
2247    arg_types = {"this": True, "expression": False, "else_": False}
2248
2249
2250class MultitableInserts(Expression):
2251    arg_types = {"expressions": True, "kind": True, "source": True}
2252
2253
2254class OnConflict(Expression):
2255    arg_types = {
2256        "duplicate": False,
2257        "expressions": False,
2258        "action": False,
2259        "conflict_keys": False,
2260        "constraint": False,
2261    }
2262
2263
2264class OnCondition(Expression):
2265    arg_types = {"error": False, "empty": False, "null": False}
2266
2267
2268class Returning(Expression):
2269    arg_types = {"expressions": True, "into": False}
2270
2271
2272# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2273class Introducer(Expression):
2274    arg_types = {"this": True, "expression": True}
2275
2276
2277# national char, like n'utf8'
2278class National(Expression):
2279    pass
2280
2281
2282class LoadData(Expression):
2283    arg_types = {
2284        "this": True,
2285        "local": False,
2286        "overwrite": False,
2287        "inpath": True,
2288        "partition": False,
2289        "input_format": False,
2290        "serde": False,
2291    }
2292
2293
2294class Partition(Expression):
2295    arg_types = {"expressions": True}
2296
2297
2298class PartitionRange(Expression):
2299    arg_types = {"this": True, "expression": True}
2300
2301
2302# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#how-to-set-partition-expression
2303class PartitionId(Expression):
2304    pass
2305
2306
2307class Fetch(Expression):
2308    arg_types = {
2309        "direction": False,
2310        "count": False,
2311        "percent": False,
2312        "with_ties": False,
2313    }
2314
2315
2316class Group(Expression):
2317    arg_types = {
2318        "expressions": False,
2319        "grouping_sets": False,
2320        "cube": False,
2321        "rollup": False,
2322        "totals": False,
2323        "all": False,
2324    }
2325
2326
2327class Cube(Expression):
2328    arg_types = {"expressions": False}
2329
2330
2331class Rollup(Expression):
2332    arg_types = {"expressions": False}
2333
2334
2335class GroupingSets(Expression):
2336    arg_types = {"expressions": True}
2337
2338
2339class Lambda(Expression):
2340    arg_types = {"this": True, "expressions": True}
2341
2342
2343class Limit(Expression):
2344    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2345
2346
2347class Literal(Condition):
2348    arg_types = {"this": True, "is_string": True}
2349
2350    @property
2351    def hashable_args(self) -> t.Any:
2352        return (self.this, self.args.get("is_string"))
2353
2354    @classmethod
2355    def number(cls, number) -> Literal:
2356        return cls(this=str(number), is_string=False)
2357
2358    @classmethod
2359    def string(cls, string) -> Literal:
2360        return cls(this=str(string), is_string=True)
2361
2362    @property
2363    def output_name(self) -> str:
2364        return self.name
2365
2366    def to_py(self) -> int | str | Decimal:
2367        if self.is_number:
2368            try:
2369                return int(self.this)
2370            except ValueError:
2371                return Decimal(self.this)
2372        return self.this
2373
2374
2375class Join(Expression):
2376    arg_types = {
2377        "this": True,
2378        "on": False,
2379        "side": False,
2380        "kind": False,
2381        "using": False,
2382        "method": False,
2383        "global": False,
2384        "hint": False,
2385        "match_condition": False,  # Snowflake
2386    }
2387
2388    @property
2389    def method(self) -> str:
2390        return self.text("method").upper()
2391
2392    @property
2393    def kind(self) -> str:
2394        return self.text("kind").upper()
2395
2396    @property
2397    def side(self) -> str:
2398        return self.text("side").upper()
2399
2400    @property
2401    def hint(self) -> str:
2402        return self.text("hint").upper()
2403
2404    @property
2405    def alias_or_name(self) -> str:
2406        return self.this.alias_or_name
2407
2408    def on(
2409        self,
2410        *expressions: t.Optional[ExpOrStr],
2411        append: bool = True,
2412        dialect: DialectType = None,
2413        copy: bool = True,
2414        **opts,
2415    ) -> Join:
2416        """
2417        Append to or set the ON expressions.
2418
2419        Example:
2420            >>> import sqlglot
2421            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2422            'JOIN x ON y = 1'
2423
2424        Args:
2425            *expressions: the SQL code strings to parse.
2426                If an `Expression` instance is passed, it will be used as-is.
2427                Multiple expressions are combined with an AND operator.
2428            append: if `True`, AND the new expressions to any existing expression.
2429                Otherwise, this resets the expression.
2430            dialect: the dialect used to parse the input expressions.
2431            copy: if `False`, modify this expression instance in-place.
2432            opts: other options to use to parse the input expressions.
2433
2434        Returns:
2435            The modified Join expression.
2436        """
2437        join = _apply_conjunction_builder(
2438            *expressions,
2439            instance=self,
2440            arg="on",
2441            append=append,
2442            dialect=dialect,
2443            copy=copy,
2444            **opts,
2445        )
2446
2447        if join.kind == "CROSS":
2448            join.set("kind", None)
2449
2450        return join
2451
2452    def using(
2453        self,
2454        *expressions: t.Optional[ExpOrStr],
2455        append: bool = True,
2456        dialect: DialectType = None,
2457        copy: bool = True,
2458        **opts,
2459    ) -> Join:
2460        """
2461        Append to or set the USING expressions.
2462
2463        Example:
2464            >>> import sqlglot
2465            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2466            'JOIN x USING (foo, bla)'
2467
2468        Args:
2469            *expressions: the SQL code strings to parse.
2470                If an `Expression` instance is passed, it will be used as-is.
2471            append: if `True`, concatenate the new expressions to the existing "using" list.
2472                Otherwise, this resets the expression.
2473            dialect: the dialect used to parse the input expressions.
2474            copy: if `False`, modify this expression instance in-place.
2475            opts: other options to use to parse the input expressions.
2476
2477        Returns:
2478            The modified Join expression.
2479        """
2480        join = _apply_list_builder(
2481            *expressions,
2482            instance=self,
2483            arg="using",
2484            append=append,
2485            dialect=dialect,
2486            copy=copy,
2487            **opts,
2488        )
2489
2490        if join.kind == "CROSS":
2491            join.set("kind", None)
2492
2493        return join
2494
2495
2496class Lateral(UDTF):
2497    arg_types = {
2498        "this": True,
2499        "view": False,
2500        "outer": False,
2501        "alias": False,
2502        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2503    }
2504
2505
2506class MatchRecognizeMeasure(Expression):
2507    arg_types = {
2508        "this": True,
2509        "window_frame": False,
2510    }
2511
2512
2513class MatchRecognize(Expression):
2514    arg_types = {
2515        "partition_by": False,
2516        "order": False,
2517        "measures": False,
2518        "rows": False,
2519        "after": False,
2520        "pattern": False,
2521        "define": False,
2522        "alias": False,
2523    }
2524
2525
2526# Clickhouse FROM FINAL modifier
2527# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2528class Final(Expression):
2529    pass
2530
2531
2532class Offset(Expression):
2533    arg_types = {"this": False, "expression": True, "expressions": False}
2534
2535
2536class Order(Expression):
2537    arg_types = {"this": False, "expressions": True, "siblings": False}
2538
2539
2540# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2541class WithFill(Expression):
2542    arg_types = {
2543        "from": False,
2544        "to": False,
2545        "step": False,
2546        "interpolate": False,
2547    }
2548
2549
2550# hive specific sorts
2551# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2552class Cluster(Order):
2553    pass
2554
2555
2556class Distribute(Order):
2557    pass
2558
2559
2560class Sort(Order):
2561    pass
2562
2563
2564class Ordered(Expression):
2565    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2566
2567
2568class Property(Expression):
2569    arg_types = {"this": True, "value": True}
2570
2571
2572class AllowedValuesProperty(Expression):
2573    arg_types = {"expressions": True}
2574
2575
2576class AlgorithmProperty(Property):
2577    arg_types = {"this": True}
2578
2579
2580class AutoIncrementProperty(Property):
2581    arg_types = {"this": True}
2582
2583
2584# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2585class AutoRefreshProperty(Property):
2586    arg_types = {"this": True}
2587
2588
2589class BackupProperty(Property):
2590    arg_types = {"this": True}
2591
2592
2593class BlockCompressionProperty(Property):
2594    arg_types = {
2595        "autotemp": False,
2596        "always": False,
2597        "default": False,
2598        "manual": False,
2599        "never": False,
2600    }
2601
2602
2603class CharacterSetProperty(Property):
2604    arg_types = {"this": True, "default": True}
2605
2606
2607class ChecksumProperty(Property):
2608    arg_types = {"on": False, "default": False}
2609
2610
2611class CollateProperty(Property):
2612    arg_types = {"this": True, "default": False}
2613
2614
2615class CopyGrantsProperty(Property):
2616    arg_types = {}
2617
2618
2619class DataBlocksizeProperty(Property):
2620    arg_types = {
2621        "size": False,
2622        "units": False,
2623        "minimum": False,
2624        "maximum": False,
2625        "default": False,
2626    }
2627
2628
2629class DataDeletionProperty(Property):
2630    arg_types = {"on": True, "filter_col": False, "retention_period": False}
2631
2632
2633class DefinerProperty(Property):
2634    arg_types = {"this": True}
2635
2636
2637class DistKeyProperty(Property):
2638    arg_types = {"this": True}
2639
2640
2641# https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
2642# https://doris.apache.org/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-TABLE?_highlight=create&_highlight=table#distribution_desc
2643class DistributedByProperty(Property):
2644    arg_types = {"expressions": False, "kind": True, "buckets": False, "order": False}
2645
2646
2647class DistStyleProperty(Property):
2648    arg_types = {"this": True}
2649
2650
2651class DuplicateKeyProperty(Property):
2652    arg_types = {"expressions": True}
2653
2654
2655class EngineProperty(Property):
2656    arg_types = {"this": True}
2657
2658
2659class HeapProperty(Property):
2660    arg_types = {}
2661
2662
2663class ToTableProperty(Property):
2664    arg_types = {"this": True}
2665
2666
2667class ExecuteAsProperty(Property):
2668    arg_types = {"this": True}
2669
2670
2671class ExternalProperty(Property):
2672    arg_types = {"this": False}
2673
2674
2675class FallbackProperty(Property):
2676    arg_types = {"no": True, "protection": False}
2677
2678
2679class FileFormatProperty(Property):
2680    arg_types = {"this": True}
2681
2682
2683class FreespaceProperty(Property):
2684    arg_types = {"this": True, "percent": False}
2685
2686
2687class GlobalProperty(Property):
2688    arg_types = {}
2689
2690
2691class IcebergProperty(Property):
2692    arg_types = {}
2693
2694
2695class InheritsProperty(Property):
2696    arg_types = {"expressions": True}
2697
2698
2699class InputModelProperty(Property):
2700    arg_types = {"this": True}
2701
2702
2703class OutputModelProperty(Property):
2704    arg_types = {"this": True}
2705
2706
2707class IsolatedLoadingProperty(Property):
2708    arg_types = {"no": False, "concurrent": False, "target": False}
2709
2710
2711class JournalProperty(Property):
2712    arg_types = {
2713        "no": False,
2714        "dual": False,
2715        "before": False,
2716        "local": False,
2717        "after": False,
2718    }
2719
2720
2721class LanguageProperty(Property):
2722    arg_types = {"this": True}
2723
2724
2725# spark ddl
2726class ClusteredByProperty(Property):
2727    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2728
2729
2730class DictProperty(Property):
2731    arg_types = {"this": True, "kind": True, "settings": False}
2732
2733
2734class DictSubProperty(Property):
2735    pass
2736
2737
2738class DictRange(Property):
2739    arg_types = {"this": True, "min": True, "max": True}
2740
2741
2742class DynamicProperty(Property):
2743    arg_types = {}
2744
2745
2746# Clickhouse CREATE ... ON CLUSTER modifier
2747# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2748class OnCluster(Property):
2749    arg_types = {"this": True}
2750
2751
2752# Clickhouse EMPTY table "property"
2753class EmptyProperty(Property):
2754    arg_types = {}
2755
2756
2757class LikeProperty(Property):
2758    arg_types = {"this": True, "expressions": False}
2759
2760
2761class LocationProperty(Property):
2762    arg_types = {"this": True}
2763
2764
2765class LockProperty(Property):
2766    arg_types = {"this": True}
2767
2768
2769class LockingProperty(Property):
2770    arg_types = {
2771        "this": False,
2772        "kind": True,
2773        "for_or_in": False,
2774        "lock_type": True,
2775        "override": False,
2776    }
2777
2778
2779class LogProperty(Property):
2780    arg_types = {"no": True}
2781
2782
2783class MaterializedProperty(Property):
2784    arg_types = {"this": False}
2785
2786
2787class MergeBlockRatioProperty(Property):
2788    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2789
2790
2791class NoPrimaryIndexProperty(Property):
2792    arg_types = {}
2793
2794
2795class OnProperty(Property):
2796    arg_types = {"this": True}
2797
2798
2799class OnCommitProperty(Property):
2800    arg_types = {"delete": False}
2801
2802
2803class PartitionedByProperty(Property):
2804    arg_types = {"this": True}
2805
2806
2807# https://www.postgresql.org/docs/current/sql-createtable.html
2808class PartitionBoundSpec(Expression):
2809    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2810    arg_types = {
2811        "this": False,
2812        "expression": False,
2813        "from_expressions": False,
2814        "to_expressions": False,
2815    }
2816
2817
2818class PartitionedOfProperty(Property):
2819    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2820    arg_types = {"this": True, "expression": True}
2821
2822
2823class StreamingTableProperty(Property):
2824    arg_types = {}
2825
2826
2827class RemoteWithConnectionModelProperty(Property):
2828    arg_types = {"this": True}
2829
2830
2831class ReturnsProperty(Property):
2832    arg_types = {"this": False, "is_table": False, "table": False, "null": False}
2833
2834
2835class StrictProperty(Property):
2836    arg_types = {}
2837
2838
2839class RowFormatProperty(Property):
2840    arg_types = {"this": True}
2841
2842
2843class RowFormatDelimitedProperty(Property):
2844    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2845    arg_types = {
2846        "fields": False,
2847        "escaped": False,
2848        "collection_items": False,
2849        "map_keys": False,
2850        "lines": False,
2851        "null": False,
2852        "serde": False,
2853    }
2854
2855
2856class RowFormatSerdeProperty(Property):
2857    arg_types = {"this": True, "serde_properties": False}
2858
2859
2860# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2861class QueryTransform(Expression):
2862    arg_types = {
2863        "expressions": True,
2864        "command_script": True,
2865        "schema": False,
2866        "row_format_before": False,
2867        "record_writer": False,
2868        "row_format_after": False,
2869        "record_reader": False,
2870    }
2871
2872
2873class SampleProperty(Property):
2874    arg_types = {"this": True}
2875
2876
2877# https://prestodb.io/docs/current/sql/create-view.html#synopsis
2878class SecurityProperty(Property):
2879    arg_types = {"this": True}
2880
2881
2882class SchemaCommentProperty(Property):
2883    arg_types = {"this": True}
2884
2885
2886class SerdeProperties(Property):
2887    arg_types = {"expressions": True, "with": False}
2888
2889
2890class SetProperty(Property):
2891    arg_types = {"multi": True}
2892
2893
2894class SharingProperty(Property):
2895    arg_types = {"this": False}
2896
2897
2898class SetConfigProperty(Property):
2899    arg_types = {"this": True}
2900
2901
2902class SettingsProperty(Property):
2903    arg_types = {"expressions": True}
2904
2905
2906class SortKeyProperty(Property):
2907    arg_types = {"this": True, "compound": False}
2908
2909
2910class SqlReadWriteProperty(Property):
2911    arg_types = {"this": True}
2912
2913
2914class SqlSecurityProperty(Property):
2915    arg_types = {"definer": True}
2916
2917
2918class StabilityProperty(Property):
2919    arg_types = {"this": True}
2920
2921
2922class TemporaryProperty(Property):
2923    arg_types = {"this": False}
2924
2925
2926class SecureProperty(Property):
2927    arg_types = {}
2928
2929
2930class TransformModelProperty(Property):
2931    arg_types = {"expressions": True}
2932
2933
2934class TransientProperty(Property):
2935    arg_types = {"this": False}
2936
2937
2938class UnloggedProperty(Property):
2939    arg_types = {}
2940
2941
2942# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16
2943class ViewAttributeProperty(Property):
2944    arg_types = {"this": True}
2945
2946
2947class VolatileProperty(Property):
2948    arg_types = {"this": False}
2949
2950
2951class WithDataProperty(Property):
2952    arg_types = {"no": True, "statistics": False}
2953
2954
2955class WithJournalTableProperty(Property):
2956    arg_types = {"this": True}
2957
2958
2959class WithSchemaBindingProperty(Property):
2960    arg_types = {"this": True}
2961
2962
2963class WithSystemVersioningProperty(Property):
2964    arg_types = {
2965        "on": False,
2966        "this": False,
2967        "data_consistency": False,
2968        "retention_period": False,
2969        "with": True,
2970    }
2971
2972
2973class Properties(Expression):
2974    arg_types = {"expressions": True}
2975
2976    NAME_TO_PROPERTY = {
2977        "ALGORITHM": AlgorithmProperty,
2978        "AUTO_INCREMENT": AutoIncrementProperty,
2979        "CHARACTER SET": CharacterSetProperty,
2980        "CLUSTERED_BY": ClusteredByProperty,
2981        "COLLATE": CollateProperty,
2982        "COMMENT": SchemaCommentProperty,
2983        "DEFINER": DefinerProperty,
2984        "DISTKEY": DistKeyProperty,
2985        "DISTRIBUTED_BY": DistributedByProperty,
2986        "DISTSTYLE": DistStyleProperty,
2987        "ENGINE": EngineProperty,
2988        "EXECUTE AS": ExecuteAsProperty,
2989        "FORMAT": FileFormatProperty,
2990        "LANGUAGE": LanguageProperty,
2991        "LOCATION": LocationProperty,
2992        "LOCK": LockProperty,
2993        "PARTITIONED_BY": PartitionedByProperty,
2994        "RETURNS": ReturnsProperty,
2995        "ROW_FORMAT": RowFormatProperty,
2996        "SORTKEY": SortKeyProperty,
2997    }
2998
2999    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
3000
3001    # CREATE property locations
3002    # Form: schema specified
3003    #   create [POST_CREATE]
3004    #     table a [POST_NAME]
3005    #     (b int) [POST_SCHEMA]
3006    #     with ([POST_WITH])
3007    #     index (b) [POST_INDEX]
3008    #
3009    # Form: alias selection
3010    #   create [POST_CREATE]
3011    #     table a [POST_NAME]
3012    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
3013    #     index (c) [POST_INDEX]
3014    class Location(AutoName):
3015        POST_CREATE = auto()
3016        POST_NAME = auto()
3017        POST_SCHEMA = auto()
3018        POST_WITH = auto()
3019        POST_ALIAS = auto()
3020        POST_EXPRESSION = auto()
3021        POST_INDEX = auto()
3022        UNSUPPORTED = auto()
3023
3024    @classmethod
3025    def from_dict(cls, properties_dict: t.Dict) -> Properties:
3026        expressions = []
3027        for key, value in properties_dict.items():
3028            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
3029            if property_cls:
3030                expressions.append(property_cls(this=convert(value)))
3031            else:
3032                expressions.append(Property(this=Literal.string(key), value=convert(value)))
3033
3034        return cls(expressions=expressions)
3035
3036
3037class Qualify(Expression):
3038    pass
3039
3040
3041class InputOutputFormat(Expression):
3042    arg_types = {"input_format": False, "output_format": False}
3043
3044
3045# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
3046class Return(Expression):
3047    pass
3048
3049
3050class Reference(Expression):
3051    arg_types = {"this": True, "expressions": False, "options": False}
3052
3053
3054class Tuple(Expression):
3055    arg_types = {"expressions": False}
3056
3057    def isin(
3058        self,
3059        *expressions: t.Any,
3060        query: t.Optional[ExpOrStr] = None,
3061        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
3062        copy: bool = True,
3063        **opts,
3064    ) -> In:
3065        return In(
3066            this=maybe_copy(self, copy),
3067            expressions=[convert(e, copy=copy) for e in expressions],
3068            query=maybe_parse(query, copy=copy, **opts) if query else None,
3069            unnest=(
3070                Unnest(
3071                    expressions=[
3072                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
3073                        for e in ensure_list(unnest)
3074                    ]
3075                )
3076                if unnest
3077                else None
3078            ),
3079        )
3080
3081
3082QUERY_MODIFIERS = {
3083    "match": False,
3084    "laterals": False,
3085    "joins": False,
3086    "connect": False,
3087    "pivots": False,
3088    "prewhere": False,
3089    "where": False,
3090    "group": False,
3091    "having": False,
3092    "qualify": False,
3093    "windows": False,
3094    "distribute": False,
3095    "sort": False,
3096    "cluster": False,
3097    "order": False,
3098    "limit": False,
3099    "offset": False,
3100    "locks": False,
3101    "sample": False,
3102    "settings": False,
3103    "format": False,
3104    "options": False,
3105}
3106
3107
3108# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
3109# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
3110class QueryOption(Expression):
3111    arg_types = {"this": True, "expression": False}
3112
3113
3114# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
3115class WithTableHint(Expression):
3116    arg_types = {"expressions": True}
3117
3118
3119# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
3120class IndexTableHint(Expression):
3121    arg_types = {"this": True, "expressions": False, "target": False}
3122
3123
3124# https://docs.snowflake.com/en/sql-reference/constructs/at-before
3125class HistoricalData(Expression):
3126    arg_types = {"this": True, "kind": True, "expression": True}
3127
3128
3129class Table(Expression):
3130    arg_types = {
3131        "this": False,
3132        "alias": False,
3133        "db": False,
3134        "catalog": False,
3135        "laterals": False,
3136        "joins": False,
3137        "pivots": False,
3138        "hints": False,
3139        "system_time": False,
3140        "version": False,
3141        "format": False,
3142        "pattern": False,
3143        "ordinality": False,
3144        "when": False,
3145        "only": False,
3146        "partition": False,
3147        "changes": False,
3148        "rows_from": False,
3149        "sample": False,
3150    }
3151
3152    @property
3153    def name(self) -> str:
3154        if isinstance(self.this, Func):
3155            return ""
3156        return self.this.name
3157
3158    @property
3159    def db(self) -> str:
3160        return self.text("db")
3161
3162    @property
3163    def catalog(self) -> str:
3164        return self.text("catalog")
3165
3166    @property
3167    def selects(self) -> t.List[Expression]:
3168        return []
3169
3170    @property
3171    def named_selects(self) -> t.List[str]:
3172        return []
3173
3174    @property
3175    def parts(self) -> t.List[Expression]:
3176        """Return the parts of a table in order catalog, db, table."""
3177        parts: t.List[Expression] = []
3178
3179        for arg in ("catalog", "db", "this"):
3180            part = self.args.get(arg)
3181
3182            if isinstance(part, Dot):
3183                parts.extend(part.flatten())
3184            elif isinstance(part, Expression):
3185                parts.append(part)
3186
3187        return parts
3188
3189    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
3190        parts = self.parts
3191        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
3192        alias = self.args.get("alias")
3193        if alias:
3194            col = alias_(col, alias.this, copy=copy)
3195        return col
3196
3197
3198class SetOperation(Query):
3199    arg_types = {
3200        "with": False,
3201        "this": True,
3202        "expression": True,
3203        "distinct": False,
3204        "by_name": False,
3205        **QUERY_MODIFIERS,
3206    }
3207
3208    def select(
3209        self: S,
3210        *expressions: t.Optional[ExpOrStr],
3211        append: bool = True,
3212        dialect: DialectType = None,
3213        copy: bool = True,
3214        **opts,
3215    ) -> S:
3216        this = maybe_copy(self, copy)
3217        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3218        this.expression.unnest().select(
3219            *expressions, append=append, dialect=dialect, copy=False, **opts
3220        )
3221        return this
3222
3223    @property
3224    def named_selects(self) -> t.List[str]:
3225        return self.this.unnest().named_selects
3226
3227    @property
3228    def is_star(self) -> bool:
3229        return self.this.is_star or self.expression.is_star
3230
3231    @property
3232    def selects(self) -> t.List[Expression]:
3233        return self.this.unnest().selects
3234
3235    @property
3236    def left(self) -> Query:
3237        return self.this
3238
3239    @property
3240    def right(self) -> Query:
3241        return self.expression
3242
3243
3244class Union(SetOperation):
3245    pass
3246
3247
3248class Except(SetOperation):
3249    pass
3250
3251
3252class Intersect(SetOperation):
3253    pass
3254
3255
3256class Update(Expression):
3257    arg_types = {
3258        "with": False,
3259        "this": False,
3260        "expressions": True,
3261        "from": False,
3262        "where": False,
3263        "returning": False,
3264        "order": False,
3265        "limit": False,
3266    }
3267
3268
3269class Values(UDTF):
3270    arg_types = {"expressions": True, "alias": False}
3271
3272
3273class Var(Expression):
3274    pass
3275
3276
3277class Version(Expression):
3278    """
3279    Time travel, iceberg, bigquery etc
3280    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
3281    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
3282    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
3283    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
3284    this is either TIMESTAMP or VERSION
3285    kind is ("AS OF", "BETWEEN")
3286    """
3287
3288    arg_types = {"this": True, "kind": True, "expression": False}
3289
3290
3291class Schema(Expression):
3292    arg_types = {"this": False, "expressions": False}
3293
3294
3295# https://dev.mysql.com/doc/refman/8.0/en/select.html
3296# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
3297class Lock(Expression):
3298    arg_types = {"update": True, "expressions": False, "wait": False}
3299
3300
3301class Select(Query):
3302    arg_types = {
3303        "with": False,
3304        "kind": False,
3305        "expressions": False,
3306        "hint": False,
3307        "distinct": False,
3308        "into": False,
3309        "from": False,
3310        **QUERY_MODIFIERS,
3311    }
3312
3313    def from_(
3314        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3315    ) -> Select:
3316        """
3317        Set the FROM expression.
3318
3319        Example:
3320            >>> Select().from_("tbl").select("x").sql()
3321            'SELECT x FROM tbl'
3322
3323        Args:
3324            expression : the SQL code strings to parse.
3325                If a `From` instance is passed, this is used as-is.
3326                If another `Expression` instance is passed, it will be wrapped in a `From`.
3327            dialect: the dialect used to parse the input expression.
3328            copy: if `False`, modify this expression instance in-place.
3329            opts: other options to use to parse the input expressions.
3330
3331        Returns:
3332            The modified Select expression.
3333        """
3334        return _apply_builder(
3335            expression=expression,
3336            instance=self,
3337            arg="from",
3338            into=From,
3339            prefix="FROM",
3340            dialect=dialect,
3341            copy=copy,
3342            **opts,
3343        )
3344
3345    def group_by(
3346        self,
3347        *expressions: t.Optional[ExpOrStr],
3348        append: bool = True,
3349        dialect: DialectType = None,
3350        copy: bool = True,
3351        **opts,
3352    ) -> Select:
3353        """
3354        Set the GROUP BY expression.
3355
3356        Example:
3357            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3358            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3359
3360        Args:
3361            *expressions: the SQL code strings to parse.
3362                If a `Group` instance is passed, this is used as-is.
3363                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3364                If nothing is passed in then a group by is not applied to the expression
3365            append: if `True`, add to any existing expressions.
3366                Otherwise, this flattens all the `Group` expression into a single expression.
3367            dialect: the dialect used to parse the input expression.
3368            copy: if `False`, modify this expression instance in-place.
3369            opts: other options to use to parse the input expressions.
3370
3371        Returns:
3372            The modified Select expression.
3373        """
3374        if not expressions:
3375            return self if not copy else self.copy()
3376
3377        return _apply_child_list_builder(
3378            *expressions,
3379            instance=self,
3380            arg="group",
3381            append=append,
3382            copy=copy,
3383            prefix="GROUP BY",
3384            into=Group,
3385            dialect=dialect,
3386            **opts,
3387        )
3388
3389    def sort_by(
3390        self,
3391        *expressions: t.Optional[ExpOrStr],
3392        append: bool = True,
3393        dialect: DialectType = None,
3394        copy: bool = True,
3395        **opts,
3396    ) -> Select:
3397        """
3398        Set the SORT BY expression.
3399
3400        Example:
3401            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3402            'SELECT x FROM tbl SORT BY x DESC'
3403
3404        Args:
3405            *expressions: the SQL code strings to parse.
3406                If a `Group` instance is passed, this is used as-is.
3407                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3408            append: if `True`, add to any existing expressions.
3409                Otherwise, this flattens all the `Order` expression into a single expression.
3410            dialect: the dialect used to parse the input expression.
3411            copy: if `False`, modify this expression instance in-place.
3412            opts: other options to use to parse the input expressions.
3413
3414        Returns:
3415            The modified Select expression.
3416        """
3417        return _apply_child_list_builder(
3418            *expressions,
3419            instance=self,
3420            arg="sort",
3421            append=append,
3422            copy=copy,
3423            prefix="SORT BY",
3424            into=Sort,
3425            dialect=dialect,
3426            **opts,
3427        )
3428
3429    def cluster_by(
3430        self,
3431        *expressions: t.Optional[ExpOrStr],
3432        append: bool = True,
3433        dialect: DialectType = None,
3434        copy: bool = True,
3435        **opts,
3436    ) -> Select:
3437        """
3438        Set the CLUSTER BY expression.
3439
3440        Example:
3441            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3442            'SELECT x FROM tbl CLUSTER BY x DESC'
3443
3444        Args:
3445            *expressions: the SQL code strings to parse.
3446                If a `Group` instance is passed, this is used as-is.
3447                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3448            append: if `True`, add to any existing expressions.
3449                Otherwise, this flattens all the `Order` expression into a single expression.
3450            dialect: the dialect used to parse the input expression.
3451            copy: if `False`, modify this expression instance in-place.
3452            opts: other options to use to parse the input expressions.
3453
3454        Returns:
3455            The modified Select expression.
3456        """
3457        return _apply_child_list_builder(
3458            *expressions,
3459            instance=self,
3460            arg="cluster",
3461            append=append,
3462            copy=copy,
3463            prefix="CLUSTER BY",
3464            into=Cluster,
3465            dialect=dialect,
3466            **opts,
3467        )
3468
3469    def select(
3470        self,
3471        *expressions: t.Optional[ExpOrStr],
3472        append: bool = True,
3473        dialect: DialectType = None,
3474        copy: bool = True,
3475        **opts,
3476    ) -> Select:
3477        return _apply_list_builder(
3478            *expressions,
3479            instance=self,
3480            arg="expressions",
3481            append=append,
3482            dialect=dialect,
3483            into=Expression,
3484            copy=copy,
3485            **opts,
3486        )
3487
3488    def lateral(
3489        self,
3490        *expressions: t.Optional[ExpOrStr],
3491        append: bool = True,
3492        dialect: DialectType = None,
3493        copy: bool = True,
3494        **opts,
3495    ) -> Select:
3496        """
3497        Append to or set the LATERAL expressions.
3498
3499        Example:
3500            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3501            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3502
3503        Args:
3504            *expressions: the SQL code strings to parse.
3505                If an `Expression` instance is passed, it will be used as-is.
3506            append: if `True`, add to any existing expressions.
3507                Otherwise, this resets the expressions.
3508            dialect: the dialect used to parse the input expressions.
3509            copy: if `False`, modify this expression instance in-place.
3510            opts: other options to use to parse the input expressions.
3511
3512        Returns:
3513            The modified Select expression.
3514        """
3515        return _apply_list_builder(
3516            *expressions,
3517            instance=self,
3518            arg="laterals",
3519            append=append,
3520            into=Lateral,
3521            prefix="LATERAL VIEW",
3522            dialect=dialect,
3523            copy=copy,
3524            **opts,
3525        )
3526
3527    def join(
3528        self,
3529        expression: ExpOrStr,
3530        on: t.Optional[ExpOrStr] = None,
3531        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3532        append: bool = True,
3533        join_type: t.Optional[str] = None,
3534        join_alias: t.Optional[Identifier | str] = None,
3535        dialect: DialectType = None,
3536        copy: bool = True,
3537        **opts,
3538    ) -> Select:
3539        """
3540        Append to or set the JOIN expressions.
3541
3542        Example:
3543            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3544            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3545
3546            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3547            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3548
3549            Use `join_type` to change the type of join:
3550
3551            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3552            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3553
3554        Args:
3555            expression: the SQL code string to parse.
3556                If an `Expression` instance is passed, it will be used as-is.
3557            on: optionally specify the join "on" criteria as a SQL string.
3558                If an `Expression` instance is passed, it will be used as-is.
3559            using: optionally specify the join "using" criteria as a SQL string.
3560                If an `Expression` instance is passed, it will be used as-is.
3561            append: if `True`, add to any existing expressions.
3562                Otherwise, this resets the expressions.
3563            join_type: if set, alter the parsed join type.
3564            join_alias: an optional alias for the joined source.
3565            dialect: the dialect used to parse the input expressions.
3566            copy: if `False`, modify this expression instance in-place.
3567            opts: other options to use to parse the input expressions.
3568
3569        Returns:
3570            Select: the modified expression.
3571        """
3572        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3573
3574        try:
3575            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3576        except ParseError:
3577            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3578
3579        join = expression if isinstance(expression, Join) else Join(this=expression)
3580
3581        if isinstance(join.this, Select):
3582            join.this.replace(join.this.subquery())
3583
3584        if join_type:
3585            method: t.Optional[Token]
3586            side: t.Optional[Token]
3587            kind: t.Optional[Token]
3588
3589            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3590
3591            if method:
3592                join.set("method", method.text)
3593            if side:
3594                join.set("side", side.text)
3595            if kind:
3596                join.set("kind", kind.text)
3597
3598        if on:
3599            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3600            join.set("on", on)
3601
3602        if using:
3603            join = _apply_list_builder(
3604                *ensure_list(using),
3605                instance=join,
3606                arg="using",
3607                append=append,
3608                copy=copy,
3609                into=Identifier,
3610                **opts,
3611            )
3612
3613        if join_alias:
3614            join.set("this", alias_(join.this, join_alias, table=True))
3615
3616        return _apply_list_builder(
3617            join,
3618            instance=self,
3619            arg="joins",
3620            append=append,
3621            copy=copy,
3622            **opts,
3623        )
3624
3625    def where(
3626        self,
3627        *expressions: t.Optional[ExpOrStr],
3628        append: bool = True,
3629        dialect: DialectType = None,
3630        copy: bool = True,
3631        **opts,
3632    ) -> Select:
3633        """
3634        Append to or set the WHERE expressions.
3635
3636        Example:
3637            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3638            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3639
3640        Args:
3641            *expressions: the SQL code strings to parse.
3642                If an `Expression` instance is passed, it will be used as-is.
3643                Multiple expressions are combined with an AND operator.
3644            append: if `True`, AND the new expressions to any existing expression.
3645                Otherwise, this resets the expression.
3646            dialect: the dialect used to parse the input expressions.
3647            copy: if `False`, modify this expression instance in-place.
3648            opts: other options to use to parse the input expressions.
3649
3650        Returns:
3651            Select: the modified expression.
3652        """
3653        return _apply_conjunction_builder(
3654            *expressions,
3655            instance=self,
3656            arg="where",
3657            append=append,
3658            into=Where,
3659            dialect=dialect,
3660            copy=copy,
3661            **opts,
3662        )
3663
3664    def having(
3665        self,
3666        *expressions: t.Optional[ExpOrStr],
3667        append: bool = True,
3668        dialect: DialectType = None,
3669        copy: bool = True,
3670        **opts,
3671    ) -> Select:
3672        """
3673        Append to or set the HAVING expressions.
3674
3675        Example:
3676            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3677            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3678
3679        Args:
3680            *expressions: the SQL code strings to parse.
3681                If an `Expression` instance is passed, it will be used as-is.
3682                Multiple expressions are combined with an AND operator.
3683            append: if `True`, AND the new expressions to any existing expression.
3684                Otherwise, this resets the expression.
3685            dialect: the dialect used to parse the input expressions.
3686            copy: if `False`, modify this expression instance in-place.
3687            opts: other options to use to parse the input expressions.
3688
3689        Returns:
3690            The modified Select expression.
3691        """
3692        return _apply_conjunction_builder(
3693            *expressions,
3694            instance=self,
3695            arg="having",
3696            append=append,
3697            into=Having,
3698            dialect=dialect,
3699            copy=copy,
3700            **opts,
3701        )
3702
3703    def window(
3704        self,
3705        *expressions: t.Optional[ExpOrStr],
3706        append: bool = True,
3707        dialect: DialectType = None,
3708        copy: bool = True,
3709        **opts,
3710    ) -> Select:
3711        return _apply_list_builder(
3712            *expressions,
3713            instance=self,
3714            arg="windows",
3715            append=append,
3716            into=Window,
3717            dialect=dialect,
3718            copy=copy,
3719            **opts,
3720        )
3721
3722    def qualify(
3723        self,
3724        *expressions: t.Optional[ExpOrStr],
3725        append: bool = True,
3726        dialect: DialectType = None,
3727        copy: bool = True,
3728        **opts,
3729    ) -> Select:
3730        return _apply_conjunction_builder(
3731            *expressions,
3732            instance=self,
3733            arg="qualify",
3734            append=append,
3735            into=Qualify,
3736            dialect=dialect,
3737            copy=copy,
3738            **opts,
3739        )
3740
3741    def distinct(
3742        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3743    ) -> Select:
3744        """
3745        Set the OFFSET expression.
3746
3747        Example:
3748            >>> Select().from_("tbl").select("x").distinct().sql()
3749            'SELECT DISTINCT x FROM tbl'
3750
3751        Args:
3752            ons: the expressions to distinct on
3753            distinct: whether the Select should be distinct
3754            copy: if `False`, modify this expression instance in-place.
3755
3756        Returns:
3757            Select: the modified expression.
3758        """
3759        instance = maybe_copy(self, copy)
3760        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3761        instance.set("distinct", Distinct(on=on) if distinct else None)
3762        return instance
3763
3764    def ctas(
3765        self,
3766        table: ExpOrStr,
3767        properties: t.Optional[t.Dict] = None,
3768        dialect: DialectType = None,
3769        copy: bool = True,
3770        **opts,
3771    ) -> Create:
3772        """
3773        Convert this expression to a CREATE TABLE AS statement.
3774
3775        Example:
3776            >>> Select().select("*").from_("tbl").ctas("x").sql()
3777            'CREATE TABLE x AS SELECT * FROM tbl'
3778
3779        Args:
3780            table: the SQL code string to parse as the table name.
3781                If another `Expression` instance is passed, it will be used as-is.
3782            properties: an optional mapping of table properties
3783            dialect: the dialect used to parse the input table.
3784            copy: if `False`, modify this expression instance in-place.
3785            opts: other options to use to parse the input table.
3786
3787        Returns:
3788            The new Create expression.
3789        """
3790        instance = maybe_copy(self, copy)
3791        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3792
3793        properties_expression = None
3794        if properties:
3795            properties_expression = Properties.from_dict(properties)
3796
3797        return Create(
3798            this=table_expression,
3799            kind="TABLE",
3800            expression=instance,
3801            properties=properties_expression,
3802        )
3803
3804    def lock(self, update: bool = True, copy: bool = True) -> Select:
3805        """
3806        Set the locking read mode for this expression.
3807
3808        Examples:
3809            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3810            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3811
3812            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3813            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3814
3815        Args:
3816            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3817            copy: if `False`, modify this expression instance in-place.
3818
3819        Returns:
3820            The modified expression.
3821        """
3822        inst = maybe_copy(self, copy)
3823        inst.set("locks", [Lock(update=update)])
3824
3825        return inst
3826
3827    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3828        """
3829        Set hints for this expression.
3830
3831        Examples:
3832            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3833            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3834
3835        Args:
3836            hints: The SQL code strings to parse as the hints.
3837                If an `Expression` instance is passed, it will be used as-is.
3838            dialect: The dialect used to parse the hints.
3839            copy: If `False`, modify this expression instance in-place.
3840
3841        Returns:
3842            The modified expression.
3843        """
3844        inst = maybe_copy(self, copy)
3845        inst.set(
3846            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3847        )
3848
3849        return inst
3850
3851    @property
3852    def named_selects(self) -> t.List[str]:
3853        return [e.output_name for e in self.expressions if e.alias_or_name]
3854
3855    @property
3856    def is_star(self) -> bool:
3857        return any(expression.is_star for expression in self.expressions)
3858
3859    @property
3860    def selects(self) -> t.List[Expression]:
3861        return self.expressions
3862
3863
3864UNWRAPPED_QUERIES = (Select, SetOperation)
3865
3866
3867class Subquery(DerivedTable, Query):
3868    arg_types = {
3869        "this": True,
3870        "alias": False,
3871        "with": False,
3872        **QUERY_MODIFIERS,
3873    }
3874
3875    def unnest(self):
3876        """Returns the first non subquery."""
3877        expression = self
3878        while isinstance(expression, Subquery):
3879            expression = expression.this
3880        return expression
3881
3882    def unwrap(self) -> Subquery:
3883        expression = self
3884        while expression.same_parent and expression.is_wrapper:
3885            expression = t.cast(Subquery, expression.parent)
3886        return expression
3887
3888    def select(
3889        self,
3890        *expressions: t.Optional[ExpOrStr],
3891        append: bool = True,
3892        dialect: DialectType = None,
3893        copy: bool = True,
3894        **opts,
3895    ) -> Subquery:
3896        this = maybe_copy(self, copy)
3897        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3898        return this
3899
3900    @property
3901    def is_wrapper(self) -> bool:
3902        """
3903        Whether this Subquery acts as a simple wrapper around another expression.
3904
3905        SELECT * FROM (((SELECT * FROM t)))
3906                      ^
3907                      This corresponds to a "wrapper" Subquery node
3908        """
3909        return all(v is None for k, v in self.args.items() if k != "this")
3910
3911    @property
3912    def is_star(self) -> bool:
3913        return self.this.is_star
3914
3915    @property
3916    def output_name(self) -> str:
3917        return self.alias
3918
3919
3920class TableSample(Expression):
3921    arg_types = {
3922        "expressions": False,
3923        "method": False,
3924        "bucket_numerator": False,
3925        "bucket_denominator": False,
3926        "bucket_field": False,
3927        "percent": False,
3928        "rows": False,
3929        "size": False,
3930        "seed": False,
3931    }
3932
3933
3934class Tag(Expression):
3935    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3936
3937    arg_types = {
3938        "this": False,
3939        "prefix": False,
3940        "postfix": False,
3941    }
3942
3943
3944# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3945# https://duckdb.org/docs/sql/statements/pivot
3946class Pivot(Expression):
3947    arg_types = {
3948        "this": False,
3949        "alias": False,
3950        "expressions": False,
3951        "field": False,
3952        "unpivot": False,
3953        "using": False,
3954        "group": False,
3955        "columns": False,
3956        "include_nulls": False,
3957        "default_on_null": False,
3958    }
3959
3960    @property
3961    def unpivot(self) -> bool:
3962        return bool(self.args.get("unpivot"))
3963
3964
3965class Window(Condition):
3966    arg_types = {
3967        "this": True,
3968        "partition_by": False,
3969        "order": False,
3970        "spec": False,
3971        "alias": False,
3972        "over": False,
3973        "first": False,
3974    }
3975
3976
3977class WindowSpec(Expression):
3978    arg_types = {
3979        "kind": False,
3980        "start": False,
3981        "start_side": False,
3982        "end": False,
3983        "end_side": False,
3984    }
3985
3986
3987class PreWhere(Expression):
3988    pass
3989
3990
3991class Where(Expression):
3992    pass
3993
3994
3995class Star(Expression):
3996    arg_types = {"except": False, "replace": False, "rename": False}
3997
3998    @property
3999    def name(self) -> str:
4000        return "*"
4001
4002    @property
4003    def output_name(self) -> str:
4004        return self.name
4005
4006
4007class Parameter(Condition):
4008    arg_types = {"this": True, "expression": False}
4009
4010
4011class SessionParameter(Condition):
4012    arg_types = {"this": True, "kind": False}
4013
4014
4015class Placeholder(Condition):
4016    arg_types = {"this": False, "kind": False}
4017
4018    @property
4019    def name(self) -> str:
4020        return self.this or "?"
4021
4022
4023class Null(Condition):
4024    arg_types: t.Dict[str, t.Any] = {}
4025
4026    @property
4027    def name(self) -> str:
4028        return "NULL"
4029
4030    def to_py(self) -> Lit[None]:
4031        return None
4032
4033
4034class Boolean(Condition):
4035    def to_py(self) -> bool:
4036        return self.this
4037
4038
4039class DataTypeParam(Expression):
4040    arg_types = {"this": True, "expression": False}
4041
4042    @property
4043    def name(self) -> str:
4044        return self.this.name
4045
4046
4047# The `nullable` arg is helpful when transpiling types from other dialects to ClickHouse, which
4048# assumes non-nullable types by default. Values `None` and `True` mean the type is nullable.
4049class DataType(Expression):
4050    arg_types = {
4051        "this": True,
4052        "expressions": False,
4053        "nested": False,
4054        "values": False,
4055        "prefix": False,
4056        "kind": False,
4057        "nullable": False,
4058    }
4059
4060    class Type(AutoName):
4061        ARRAY = auto()
4062        AGGREGATEFUNCTION = auto()
4063        SIMPLEAGGREGATEFUNCTION = auto()
4064        BIGDECIMAL = auto()
4065        BIGINT = auto()
4066        BIGSERIAL = auto()
4067        BINARY = auto()
4068        BIT = auto()
4069        BOOLEAN = auto()
4070        BPCHAR = auto()
4071        CHAR = auto()
4072        DATE = auto()
4073        DATE32 = auto()
4074        DATEMULTIRANGE = auto()
4075        DATERANGE = auto()
4076        DATETIME = auto()
4077        DATETIME64 = auto()
4078        DECIMAL = auto()
4079        DECIMAL32 = auto()
4080        DECIMAL64 = auto()
4081        DECIMAL128 = auto()
4082        DOUBLE = auto()
4083        ENUM = auto()
4084        ENUM8 = auto()
4085        ENUM16 = auto()
4086        FIXEDSTRING = auto()
4087        FLOAT = auto()
4088        GEOGRAPHY = auto()
4089        GEOMETRY = auto()
4090        HLLSKETCH = auto()
4091        HSTORE = auto()
4092        IMAGE = auto()
4093        INET = auto()
4094        INT = auto()
4095        INT128 = auto()
4096        INT256 = auto()
4097        INT4MULTIRANGE = auto()
4098        INT4RANGE = auto()
4099        INT8MULTIRANGE = auto()
4100        INT8RANGE = auto()
4101        INTERVAL = auto()
4102        IPADDRESS = auto()
4103        IPPREFIX = auto()
4104        IPV4 = auto()
4105        IPV6 = auto()
4106        JSON = auto()
4107        JSONB = auto()
4108        LIST = auto()
4109        LONGBLOB = auto()
4110        LONGTEXT = auto()
4111        LOWCARDINALITY = auto()
4112        MAP = auto()
4113        MEDIUMBLOB = auto()
4114        MEDIUMINT = auto()
4115        MEDIUMTEXT = auto()
4116        MONEY = auto()
4117        NAME = auto()
4118        NCHAR = auto()
4119        NESTED = auto()
4120        NULL = auto()
4121        NUMMULTIRANGE = auto()
4122        NUMRANGE = auto()
4123        NVARCHAR = auto()
4124        OBJECT = auto()
4125        ROWVERSION = auto()
4126        SERIAL = auto()
4127        SET = auto()
4128        SMALLINT = auto()
4129        SMALLMONEY = auto()
4130        SMALLSERIAL = auto()
4131        STRUCT = auto()
4132        SUPER = auto()
4133        TEXT = auto()
4134        TINYBLOB = auto()
4135        TINYTEXT = auto()
4136        TIME = auto()
4137        TIMETZ = auto()
4138        TIMESTAMP = auto()
4139        TIMESTAMPNTZ = auto()
4140        TIMESTAMPLTZ = auto()
4141        TIMESTAMPTZ = auto()
4142        TIMESTAMP_S = auto()
4143        TIMESTAMP_MS = auto()
4144        TIMESTAMP_NS = auto()
4145        TINYINT = auto()
4146        TSMULTIRANGE = auto()
4147        TSRANGE = auto()
4148        TSTZMULTIRANGE = auto()
4149        TSTZRANGE = auto()
4150        UBIGINT = auto()
4151        UINT = auto()
4152        UINT128 = auto()
4153        UINT256 = auto()
4154        UMEDIUMINT = auto()
4155        UDECIMAL = auto()
4156        UNIQUEIDENTIFIER = auto()
4157        UNKNOWN = auto()  # Sentinel value, useful for type annotation
4158        USERDEFINED = "USER-DEFINED"
4159        USMALLINT = auto()
4160        UTINYINT = auto()
4161        UUID = auto()
4162        VARBINARY = auto()
4163        VARCHAR = auto()
4164        VARIANT = auto()
4165        VECTOR = auto()
4166        XML = auto()
4167        YEAR = auto()
4168        TDIGEST = auto()
4169
4170    STRUCT_TYPES = {
4171        Type.NESTED,
4172        Type.OBJECT,
4173        Type.STRUCT,
4174    }
4175
4176    ARRAY_TYPES = {
4177        Type.ARRAY,
4178        Type.LIST,
4179    }
4180
4181    NESTED_TYPES = {
4182        *STRUCT_TYPES,
4183        *ARRAY_TYPES,
4184        Type.MAP,
4185    }
4186
4187    TEXT_TYPES = {
4188        Type.CHAR,
4189        Type.NCHAR,
4190        Type.NVARCHAR,
4191        Type.TEXT,
4192        Type.VARCHAR,
4193        Type.NAME,
4194    }
4195
4196    SIGNED_INTEGER_TYPES = {
4197        Type.BIGINT,
4198        Type.INT,
4199        Type.INT128,
4200        Type.INT256,
4201        Type.MEDIUMINT,
4202        Type.SMALLINT,
4203        Type.TINYINT,
4204    }
4205
4206    UNSIGNED_INTEGER_TYPES = {
4207        Type.UBIGINT,
4208        Type.UINT,
4209        Type.UINT128,
4210        Type.UINT256,
4211        Type.UMEDIUMINT,
4212        Type.USMALLINT,
4213        Type.UTINYINT,
4214    }
4215
4216    INTEGER_TYPES = {
4217        *SIGNED_INTEGER_TYPES,
4218        *UNSIGNED_INTEGER_TYPES,
4219        Type.BIT,
4220    }
4221
4222    FLOAT_TYPES = {
4223        Type.DOUBLE,
4224        Type.FLOAT,
4225    }
4226
4227    REAL_TYPES = {
4228        *FLOAT_TYPES,
4229        Type.BIGDECIMAL,
4230        Type.DECIMAL,
4231        Type.DECIMAL32,
4232        Type.DECIMAL64,
4233        Type.DECIMAL128,
4234        Type.MONEY,
4235        Type.SMALLMONEY,
4236        Type.UDECIMAL,
4237    }
4238
4239    NUMERIC_TYPES = {
4240        *INTEGER_TYPES,
4241        *REAL_TYPES,
4242    }
4243
4244    TEMPORAL_TYPES = {
4245        Type.DATE,
4246        Type.DATE32,
4247        Type.DATETIME,
4248        Type.DATETIME64,
4249        Type.TIME,
4250        Type.TIMESTAMP,
4251        Type.TIMESTAMPNTZ,
4252        Type.TIMESTAMPLTZ,
4253        Type.TIMESTAMPTZ,
4254        Type.TIMESTAMP_MS,
4255        Type.TIMESTAMP_NS,
4256        Type.TIMESTAMP_S,
4257        Type.TIMETZ,
4258    }
4259
4260    @classmethod
4261    def build(
4262        cls,
4263        dtype: DATA_TYPE,
4264        dialect: DialectType = None,
4265        udt: bool = False,
4266        copy: bool = True,
4267        **kwargs,
4268    ) -> DataType:
4269        """
4270        Constructs a DataType object.
4271
4272        Args:
4273            dtype: the data type of interest.
4274            dialect: the dialect to use for parsing `dtype`, in case it's a string.
4275            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
4276                DataType, thus creating a user-defined type.
4277            copy: whether to copy the data type.
4278            kwargs: additional arguments to pass in the constructor of DataType.
4279
4280        Returns:
4281            The constructed DataType object.
4282        """
4283        from sqlglot import parse_one
4284
4285        if isinstance(dtype, str):
4286            if dtype.upper() == "UNKNOWN":
4287                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4288
4289            try:
4290                data_type_exp = parse_one(
4291                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4292                )
4293            except ParseError:
4294                if udt:
4295                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4296                raise
4297        elif isinstance(dtype, DataType.Type):
4298            data_type_exp = DataType(this=dtype)
4299        elif isinstance(dtype, DataType):
4300            return maybe_copy(dtype, copy)
4301        else:
4302            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4303
4304        return DataType(**{**data_type_exp.args, **kwargs})
4305
4306    def is_type(self, *dtypes: DATA_TYPE, check_nullable: bool = False) -> bool:
4307        """
4308        Checks whether this DataType matches one of the provided data types. Nested types or precision
4309        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4310
4311        Args:
4312            dtypes: the data types to compare this DataType to.
4313            check_nullable: whether to take the NULLABLE type constructor into account for the comparison.
4314                If false, it means that NULLABLE<INT> is equivalent to INT.
4315
4316        Returns:
4317            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4318        """
4319        self_is_nullable = self.args.get("nullable")
4320        for dtype in dtypes:
4321            other_type = DataType.build(dtype, copy=False, udt=True)
4322            other_is_nullable = other_type.args.get("nullable")
4323            if (
4324                other_type.expressions
4325                or (check_nullable and (self_is_nullable or other_is_nullable))
4326                or self.this == DataType.Type.USERDEFINED
4327                or other_type.this == DataType.Type.USERDEFINED
4328            ):
4329                matches = self == other_type
4330            else:
4331                matches = self.this == other_type.this
4332
4333            if matches:
4334                return True
4335        return False
4336
4337
4338DATA_TYPE = t.Union[str, DataType, DataType.Type]
4339
4340
4341# https://www.postgresql.org/docs/15/datatype-pseudo.html
4342class PseudoType(DataType):
4343    arg_types = {"this": True}
4344
4345
4346# https://www.postgresql.org/docs/15/datatype-oid.html
4347class ObjectIdentifier(DataType):
4348    arg_types = {"this": True}
4349
4350
4351# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4352class SubqueryPredicate(Predicate):
4353    pass
4354
4355
4356class All(SubqueryPredicate):
4357    pass
4358
4359
4360class Any(SubqueryPredicate):
4361    pass
4362
4363
4364class Exists(SubqueryPredicate):
4365    pass
4366
4367
4368# Commands to interact with the databases or engines. For most of the command
4369# expressions we parse whatever comes after the command's name as a string.
4370class Command(Expression):
4371    arg_types = {"this": True, "expression": False}
4372
4373
4374class Transaction(Expression):
4375    arg_types = {"this": False, "modes": False, "mark": False}
4376
4377
4378class Commit(Expression):
4379    arg_types = {"chain": False, "this": False, "durability": False}
4380
4381
4382class Rollback(Expression):
4383    arg_types = {"savepoint": False, "this": False}
4384
4385
4386class Alter(Expression):
4387    arg_types = {
4388        "this": True,
4389        "kind": True,
4390        "actions": True,
4391        "exists": False,
4392        "only": False,
4393        "options": False,
4394        "cluster": False,
4395        "not_valid": False,
4396    }
4397
4398
4399class AddConstraint(Expression):
4400    arg_types = {"expressions": True}
4401
4402
4403class DropPartition(Expression):
4404    arg_types = {"expressions": True, "exists": False}
4405
4406
4407# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#replace-partition
4408class ReplacePartition(Expression):
4409    arg_types = {"expression": True, "source": True}
4410
4411
4412# Binary expressions like (ADD a b)
4413class Binary(Condition):
4414    arg_types = {"this": True, "expression": True}
4415
4416    @property
4417    def left(self) -> Expression:
4418        return self.this
4419
4420    @property
4421    def right(self) -> Expression:
4422        return self.expression
4423
4424
4425class Add(Binary):
4426    pass
4427
4428
4429class Connector(Binary):
4430    pass
4431
4432
4433class And(Connector):
4434    pass
4435
4436
4437class Or(Connector):
4438    pass
4439
4440
4441class BitwiseAnd(Binary):
4442    pass
4443
4444
4445class BitwiseLeftShift(Binary):
4446    pass
4447
4448
4449class BitwiseOr(Binary):
4450    pass
4451
4452
4453class BitwiseRightShift(Binary):
4454    pass
4455
4456
4457class BitwiseXor(Binary):
4458    pass
4459
4460
4461class Div(Binary):
4462    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4463
4464
4465class Overlaps(Binary):
4466    pass
4467
4468
4469class Dot(Binary):
4470    @property
4471    def is_star(self) -> bool:
4472        return self.expression.is_star
4473
4474    @property
4475    def name(self) -> str:
4476        return self.expression.name
4477
4478    @property
4479    def output_name(self) -> str:
4480        return self.name
4481
4482    @classmethod
4483    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4484        """Build a Dot object with a sequence of expressions."""
4485        if len(expressions) < 2:
4486            raise ValueError("Dot requires >= 2 expressions.")
4487
4488        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4489
4490    @property
4491    def parts(self) -> t.List[Expression]:
4492        """Return the parts of a table / column in order catalog, db, table."""
4493        this, *parts = self.flatten()
4494
4495        parts.reverse()
4496
4497        for arg in COLUMN_PARTS:
4498            part = this.args.get(arg)
4499
4500            if isinstance(part, Expression):
4501                parts.append(part)
4502
4503        parts.reverse()
4504        return parts
4505
4506
4507class DPipe(Binary):
4508    arg_types = {"this": True, "expression": True, "safe": False}
4509
4510
4511class EQ(Binary, Predicate):
4512    pass
4513
4514
4515class NullSafeEQ(Binary, Predicate):
4516    pass
4517
4518
4519class NullSafeNEQ(Binary, Predicate):
4520    pass
4521
4522
4523# Represents e.g. := in DuckDB which is mostly used for setting parameters
4524class PropertyEQ(Binary):
4525    pass
4526
4527
4528class Distance(Binary):
4529    pass
4530
4531
4532class Escape(Binary):
4533    pass
4534
4535
4536class Glob(Binary, Predicate):
4537    pass
4538
4539
4540class GT(Binary, Predicate):
4541    pass
4542
4543
4544class GTE(Binary, Predicate):
4545    pass
4546
4547
4548class ILike(Binary, Predicate):
4549    pass
4550
4551
4552class ILikeAny(Binary, Predicate):
4553    pass
4554
4555
4556class IntDiv(Binary):
4557    pass
4558
4559
4560class Is(Binary, Predicate):
4561    pass
4562
4563
4564class Kwarg(Binary):
4565    """Kwarg in special functions like func(kwarg => y)."""
4566
4567
4568class Like(Binary, Predicate):
4569    pass
4570
4571
4572class LikeAny(Binary, Predicate):
4573    pass
4574
4575
4576class LT(Binary, Predicate):
4577    pass
4578
4579
4580class LTE(Binary, Predicate):
4581    pass
4582
4583
4584class Mod(Binary):
4585    pass
4586
4587
4588class Mul(Binary):
4589    pass
4590
4591
4592class NEQ(Binary, Predicate):
4593    pass
4594
4595
4596# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4597class Operator(Binary):
4598    arg_types = {"this": True, "operator": True, "expression": True}
4599
4600
4601class SimilarTo(Binary, Predicate):
4602    pass
4603
4604
4605class Slice(Binary):
4606    arg_types = {"this": False, "expression": False}
4607
4608
4609class Sub(Binary):
4610    pass
4611
4612
4613# Unary Expressions
4614# (NOT a)
4615class Unary(Condition):
4616    pass
4617
4618
4619class BitwiseNot(Unary):
4620    pass
4621
4622
4623class Not(Unary):
4624    pass
4625
4626
4627class Paren(Unary):
4628    @property
4629    def output_name(self) -> str:
4630        return self.this.name
4631
4632
4633class Neg(Unary):
4634    def to_py(self) -> int | Decimal:
4635        if self.is_number:
4636            return self.this.to_py() * -1
4637        return super().to_py()
4638
4639
4640class Alias(Expression):
4641    arg_types = {"this": True, "alias": False}
4642
4643    @property
4644    def output_name(self) -> str:
4645        return self.alias
4646
4647
4648# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4649# other dialects require identifiers. This enables us to transpile between them easily.
4650class PivotAlias(Alias):
4651    pass
4652
4653
4654# Represents Snowflake's ANY [ ORDER BY ... ] syntax
4655# https://docs.snowflake.com/en/sql-reference/constructs/pivot
4656class PivotAny(Expression):
4657    arg_types = {"this": False}
4658
4659
4660class Aliases(Expression):
4661    arg_types = {"this": True, "expressions": True}
4662
4663    @property
4664    def aliases(self):
4665        return self.expressions
4666
4667
4668# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4669class AtIndex(Expression):
4670    arg_types = {"this": True, "expression": True}
4671
4672
4673class AtTimeZone(Expression):
4674    arg_types = {"this": True, "zone": True}
4675
4676
4677class FromTimeZone(Expression):
4678    arg_types = {"this": True, "zone": True}
4679
4680
4681class Between(Predicate):
4682    arg_types = {"this": True, "low": True, "high": True}
4683
4684
4685class Bracket(Condition):
4686    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4687    arg_types = {
4688        "this": True,
4689        "expressions": True,
4690        "offset": False,
4691        "safe": False,
4692        "returns_list_for_maps": False,
4693    }
4694
4695    @property
4696    def output_name(self) -> str:
4697        if len(self.expressions) == 1:
4698            return self.expressions[0].output_name
4699
4700        return super().output_name
4701
4702
4703class Distinct(Expression):
4704    arg_types = {"expressions": False, "on": False}
4705
4706
4707class In(Predicate):
4708    arg_types = {
4709        "this": True,
4710        "expressions": False,
4711        "query": False,
4712        "unnest": False,
4713        "field": False,
4714        "is_global": False,
4715    }
4716
4717
4718# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4719class ForIn(Expression):
4720    arg_types = {"this": True, "expression": True}
4721
4722
4723class TimeUnit(Expression):
4724    """Automatically converts unit arg into a var."""
4725
4726    arg_types = {"unit": False}
4727
4728    UNABBREVIATED_UNIT_NAME = {
4729        "D": "DAY",
4730        "H": "HOUR",
4731        "M": "MINUTE",
4732        "MS": "MILLISECOND",
4733        "NS": "NANOSECOND",
4734        "Q": "QUARTER",
4735        "S": "SECOND",
4736        "US": "MICROSECOND",
4737        "W": "WEEK",
4738        "Y": "YEAR",
4739    }
4740
4741    VAR_LIKE = (Column, Literal, Var)
4742
4743    def __init__(self, **args):
4744        unit = args.get("unit")
4745        if isinstance(unit, self.VAR_LIKE):
4746            args["unit"] = Var(
4747                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4748            )
4749        elif isinstance(unit, Week):
4750            unit.set("this", Var(this=unit.this.name.upper()))
4751
4752        super().__init__(**args)
4753
4754    @property
4755    def unit(self) -> t.Optional[Var | IntervalSpan]:
4756        return self.args.get("unit")
4757
4758
4759class IntervalOp(TimeUnit):
4760    arg_types = {"unit": False, "expression": True}
4761
4762    def interval(self):
4763        return Interval(
4764            this=self.expression.copy(),
4765            unit=self.unit.copy() if self.unit else None,
4766        )
4767
4768
4769# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4770# https://trino.io/docs/current/language/types.html#interval-day-to-second
4771# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4772class IntervalSpan(DataType):
4773    arg_types = {"this": True, "expression": True}
4774
4775
4776class Interval(TimeUnit):
4777    arg_types = {"this": False, "unit": False}
4778
4779
4780class IgnoreNulls(Expression):
4781    pass
4782
4783
4784class RespectNulls(Expression):
4785    pass
4786
4787
4788# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4789class HavingMax(Expression):
4790    arg_types = {"this": True, "expression": True, "max": True}
4791
4792
4793# Functions
4794class Func(Condition):
4795    """
4796    The base class for all function expressions.
4797
4798    Attributes:
4799        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4800            treated as a variable length argument and the argument's value will be stored as a list.
4801        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4802            function expression. These values are used to map this node to a name during parsing as
4803            well as to provide the function's name during SQL string generation. By default the SQL
4804            name is set to the expression's class name transformed to snake case.
4805    """
4806
4807    is_var_len_args = False
4808
4809    @classmethod
4810    def from_arg_list(cls, args):
4811        if cls.is_var_len_args:
4812            all_arg_keys = list(cls.arg_types)
4813            # If this function supports variable length argument treat the last argument as such.
4814            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4815            num_non_var = len(non_var_len_arg_keys)
4816
4817            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4818            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4819        else:
4820            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4821
4822        return cls(**args_dict)
4823
4824    @classmethod
4825    def sql_names(cls):
4826        if cls is Func:
4827            raise NotImplementedError(
4828                "SQL name is only supported by concrete function implementations"
4829            )
4830        if "_sql_names" not in cls.__dict__:
4831            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4832        return cls._sql_names
4833
4834    @classmethod
4835    def sql_name(cls):
4836        return cls.sql_names()[0]
4837
4838    @classmethod
4839    def default_parser_mappings(cls):
4840        return {name: cls.from_arg_list for name in cls.sql_names()}
4841
4842
4843class AggFunc(Func):
4844    pass
4845
4846
4847class ParameterizedAgg(AggFunc):
4848    arg_types = {"this": True, "expressions": True, "params": True}
4849
4850
4851class Abs(Func):
4852    pass
4853
4854
4855class ArgMax(AggFunc):
4856    arg_types = {"this": True, "expression": True, "count": False}
4857    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4858
4859
4860class ArgMin(AggFunc):
4861    arg_types = {"this": True, "expression": True, "count": False}
4862    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4863
4864
4865class ApproxTopK(AggFunc):
4866    arg_types = {"this": True, "expression": False, "counters": False}
4867
4868
4869class Flatten(Func):
4870    pass
4871
4872
4873# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4874class Transform(Func):
4875    arg_types = {"this": True, "expression": True}
4876
4877
4878class Anonymous(Func):
4879    arg_types = {"this": True, "expressions": False}
4880    is_var_len_args = True
4881
4882    @property
4883    def name(self) -> str:
4884        return self.this if isinstance(self.this, str) else self.this.name
4885
4886
4887class AnonymousAggFunc(AggFunc):
4888    arg_types = {"this": True, "expressions": False}
4889    is_var_len_args = True
4890
4891
4892# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4893class CombinedAggFunc(AnonymousAggFunc):
4894    arg_types = {"this": True, "expressions": False, "parts": True}
4895
4896
4897class CombinedParameterizedAgg(ParameterizedAgg):
4898    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4899
4900
4901# https://docs.snowflake.com/en/sql-reference/functions/hll
4902# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4903class Hll(AggFunc):
4904    arg_types = {"this": True, "expressions": False}
4905    is_var_len_args = True
4906
4907
4908class ApproxDistinct(AggFunc):
4909    arg_types = {"this": True, "accuracy": False}
4910    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4911
4912
4913class Array(Func):
4914    arg_types = {"expressions": False, "bracket_notation": False}
4915    is_var_len_args = True
4916
4917
4918# https://docs.snowflake.com/en/sql-reference/functions/to_array
4919class ToArray(Func):
4920    pass
4921
4922
4923# https://materialize.com/docs/sql/types/list/
4924class List(Func):
4925    arg_types = {"expressions": False}
4926    is_var_len_args = True
4927
4928
4929# String pad, kind True -> LPAD, False -> RPAD
4930class Pad(Func):
4931    arg_types = {"this": True, "expression": True, "fill_pattern": False, "is_left": True}
4932
4933
4934# https://docs.snowflake.com/en/sql-reference/functions/to_char
4935# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4936class ToChar(Func):
4937    arg_types = {"this": True, "format": False, "nlsparam": False}
4938
4939
4940# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4941# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4942class ToNumber(Func):
4943    arg_types = {
4944        "this": True,
4945        "format": False,
4946        "nlsparam": False,
4947        "precision": False,
4948        "scale": False,
4949    }
4950
4951
4952# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4953class Convert(Func):
4954    arg_types = {"this": True, "expression": True, "style": False}
4955
4956
4957class ConvertTimezone(Func):
4958    arg_types = {"source_tz": False, "target_tz": True, "timestamp": True}
4959
4960
4961class GenerateSeries(Func):
4962    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4963
4964
4965# Postgres' GENERATE_SERIES function returns a row set, i.e. it implicitly explodes when it's
4966# used in a projection, so this expression is a helper that facilitates transpilation to other
4967# dialects. For example, we'd generate UNNEST(GENERATE_SERIES(...)) in DuckDB
4968class ExplodingGenerateSeries(GenerateSeries):
4969    pass
4970
4971
4972class ArrayAgg(AggFunc):
4973    arg_types = {"this": True, "nulls_excluded": False}
4974
4975
4976class ArrayUniqueAgg(AggFunc):
4977    pass
4978
4979
4980class ArrayAll(Func):
4981    arg_types = {"this": True, "expression": True}
4982
4983
4984# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4985class ArrayAny(Func):
4986    arg_types = {"this": True, "expression": True}
4987
4988
4989class ArrayConcat(Func):
4990    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4991    arg_types = {"this": True, "expressions": False}
4992    is_var_len_args = True
4993
4994
4995class ArrayConstructCompact(Func):
4996    arg_types = {"expressions": True}
4997    is_var_len_args = True
4998
4999
5000class ArrayContains(Binary, Func):
5001    _sql_names = ["ARRAY_CONTAINS", "ARRAY_HAS"]
5002
5003
5004class ArrayContainsAll(Binary, Func):
5005    _sql_names = ["ARRAY_CONTAINS_ALL", "ARRAY_HAS_ALL"]
5006
5007
5008class ArrayFilter(Func):
5009    arg_types = {"this": True, "expression": True}
5010    _sql_names = ["FILTER", "ARRAY_FILTER"]
5011
5012
5013class ArrayToString(Func):
5014    arg_types = {"this": True, "expression": True, "null": False}
5015    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
5016
5017
5018class StringToArray(Func):
5019    arg_types = {"this": True, "expression": True, "null": False}
5020    _sql_names = ["STRING_TO_ARRAY", "SPLIT_BY_STRING"]
5021
5022
5023class ArrayOverlaps(Binary, Func):
5024    pass
5025
5026
5027class ArraySize(Func):
5028    arg_types = {"this": True, "expression": False}
5029    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
5030
5031
5032class ArraySort(Func):
5033    arg_types = {"this": True, "expression": False}
5034
5035
5036class ArraySum(Func):
5037    arg_types = {"this": True, "expression": False}
5038
5039
5040class ArrayUnionAgg(AggFunc):
5041    pass
5042
5043
5044class Avg(AggFunc):
5045    pass
5046
5047
5048class AnyValue(AggFunc):
5049    pass
5050
5051
5052class Lag(AggFunc):
5053    arg_types = {"this": True, "offset": False, "default": False}
5054
5055
5056class Lead(AggFunc):
5057    arg_types = {"this": True, "offset": False, "default": False}
5058
5059
5060# some dialects have a distinction between first and first_value, usually first is an aggregate func
5061# and first_value is a window func
5062class First(AggFunc):
5063    pass
5064
5065
5066class Last(AggFunc):
5067    pass
5068
5069
5070class FirstValue(AggFunc):
5071    pass
5072
5073
5074class LastValue(AggFunc):
5075    pass
5076
5077
5078class NthValue(AggFunc):
5079    arg_types = {"this": True, "offset": True}
5080
5081
5082class Case(Func):
5083    arg_types = {"this": False, "ifs": True, "default": False}
5084
5085    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
5086        instance = maybe_copy(self, copy)
5087        instance.append(
5088            "ifs",
5089            If(
5090                this=maybe_parse(condition, copy=copy, **opts),
5091                true=maybe_parse(then, copy=copy, **opts),
5092            ),
5093        )
5094        return instance
5095
5096    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
5097        instance = maybe_copy(self, copy)
5098        instance.set("default", maybe_parse(condition, copy=copy, **opts))
5099        return instance
5100
5101
5102class Cast(Func):
5103    arg_types = {
5104        "this": True,
5105        "to": True,
5106        "format": False,
5107        "safe": False,
5108        "action": False,
5109    }
5110
5111    @property
5112    def name(self) -> str:
5113        return self.this.name
5114
5115    @property
5116    def to(self) -> DataType:
5117        return self.args["to"]
5118
5119    @property
5120    def output_name(self) -> str:
5121        return self.name
5122
5123    def is_type(self, *dtypes: DATA_TYPE) -> bool:
5124        """
5125        Checks whether this Cast's DataType matches one of the provided data types. Nested types
5126        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
5127        array<int> != array<float>.
5128
5129        Args:
5130            dtypes: the data types to compare this Cast's DataType to.
5131
5132        Returns:
5133            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
5134        """
5135        return self.to.is_type(*dtypes)
5136
5137
5138class TryCast(Cast):
5139    pass
5140
5141
5142class Try(Func):
5143    pass
5144
5145
5146class CastToStrType(Func):
5147    arg_types = {"this": True, "to": True}
5148
5149
5150class Collate(Binary, Func):
5151    pass
5152
5153
5154class Ceil(Func):
5155    arg_types = {"this": True, "decimals": False}
5156    _sql_names = ["CEIL", "CEILING"]
5157
5158
5159class Coalesce(Func):
5160    arg_types = {"this": True, "expressions": False, "is_nvl": False}
5161    is_var_len_args = True
5162    _sql_names = ["COALESCE", "IFNULL", "NVL"]
5163
5164
5165class Chr(Func):
5166    arg_types = {"expressions": True, "charset": False}
5167    is_var_len_args = True
5168    _sql_names = ["CHR", "CHAR"]
5169
5170
5171class Concat(Func):
5172    arg_types = {"expressions": True, "safe": False, "coalesce": False}
5173    is_var_len_args = True
5174
5175
5176class ConcatWs(Concat):
5177    _sql_names = ["CONCAT_WS"]
5178
5179
5180# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
5181class ConnectByRoot(Func):
5182    pass
5183
5184
5185class Count(AggFunc):
5186    arg_types = {"this": False, "expressions": False, "big_int": False}
5187    is_var_len_args = True
5188
5189
5190class CountIf(AggFunc):
5191    _sql_names = ["COUNT_IF", "COUNTIF"]
5192
5193
5194# cube root
5195class Cbrt(Func):
5196    pass
5197
5198
5199class CurrentDate(Func):
5200    arg_types = {"this": False}
5201
5202
5203class CurrentDatetime(Func):
5204    arg_types = {"this": False}
5205
5206
5207class CurrentTime(Func):
5208    arg_types = {"this": False}
5209
5210
5211class CurrentTimestamp(Func):
5212    arg_types = {"this": False, "sysdate": False}
5213
5214
5215class CurrentUser(Func):
5216    arg_types = {"this": False}
5217
5218
5219class DateAdd(Func, IntervalOp):
5220    arg_types = {"this": True, "expression": True, "unit": False}
5221
5222
5223class DateSub(Func, IntervalOp):
5224    arg_types = {"this": True, "expression": True, "unit": False}
5225
5226
5227class DateDiff(Func, TimeUnit):
5228    _sql_names = ["DATEDIFF", "DATE_DIFF"]
5229    arg_types = {"this": True, "expression": True, "unit": False}
5230
5231
5232class DateTrunc(Func):
5233    arg_types = {"unit": True, "this": True, "zone": False}
5234
5235    def __init__(self, **args):
5236        unit = args.get("unit")
5237        if isinstance(unit, TimeUnit.VAR_LIKE):
5238            args["unit"] = Literal.string(
5239                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
5240            )
5241        elif isinstance(unit, Week):
5242            unit.set("this", Literal.string(unit.this.name.upper()))
5243
5244        super().__init__(**args)
5245
5246    @property
5247    def unit(self) -> Expression:
5248        return self.args["unit"]
5249
5250
5251# https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime
5252# expression can either be time_expr or time_zone
5253class Datetime(Func):
5254    arg_types = {"this": True, "expression": False}
5255
5256
5257class DatetimeAdd(Func, IntervalOp):
5258    arg_types = {"this": True, "expression": True, "unit": False}
5259
5260
5261class DatetimeSub(Func, IntervalOp):
5262    arg_types = {"this": True, "expression": True, "unit": False}
5263
5264
5265class DatetimeDiff(Func, TimeUnit):
5266    arg_types = {"this": True, "expression": True, "unit": False}
5267
5268
5269class DatetimeTrunc(Func, TimeUnit):
5270    arg_types = {"this": True, "unit": True, "zone": False}
5271
5272
5273class DayOfWeek(Func):
5274    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
5275
5276
5277# https://duckdb.org/docs/sql/functions/datepart.html#part-specifiers-only-usable-as-date-part-specifiers
5278# ISO day of week function in duckdb is ISODOW
5279class DayOfWeekIso(Func):
5280    _sql_names = ["DAYOFWEEK_ISO", "ISODOW"]
5281
5282
5283class DayOfMonth(Func):
5284    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
5285
5286
5287class DayOfYear(Func):
5288    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
5289
5290
5291class ToDays(Func):
5292    pass
5293
5294
5295class WeekOfYear(Func):
5296    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
5297
5298
5299class MonthsBetween(Func):
5300    arg_types = {"this": True, "expression": True, "roundoff": False}
5301
5302
5303class LastDay(Func, TimeUnit):
5304    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
5305    arg_types = {"this": True, "unit": False}
5306
5307
5308class Extract(Func):
5309    arg_types = {"this": True, "expression": True}
5310
5311
5312class Timestamp(Func):
5313    arg_types = {"this": False, "zone": False, "with_tz": False}
5314
5315
5316class TimestampAdd(Func, TimeUnit):
5317    arg_types = {"this": True, "expression": True, "unit": False}
5318
5319
5320class TimestampSub(Func, TimeUnit):
5321    arg_types = {"this": True, "expression": True, "unit": False}
5322
5323
5324class TimestampDiff(Func, TimeUnit):
5325    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
5326    arg_types = {"this": True, "expression": True, "unit": False}
5327
5328
5329class TimestampTrunc(Func, TimeUnit):
5330    arg_types = {"this": True, "unit": True, "zone": False}
5331
5332
5333class TimeAdd(Func, TimeUnit):
5334    arg_types = {"this": True, "expression": True, "unit": False}
5335
5336
5337class TimeSub(Func, TimeUnit):
5338    arg_types = {"this": True, "expression": True, "unit": False}
5339
5340
5341class TimeDiff(Func, TimeUnit):
5342    arg_types = {"this": True, "expression": True, "unit": False}
5343
5344
5345class TimeTrunc(Func, TimeUnit):
5346    arg_types = {"this": True, "unit": True, "zone": False}
5347
5348
5349class DateFromParts(Func):
5350    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
5351    arg_types = {"year": True, "month": True, "day": True}
5352
5353
5354class TimeFromParts(Func):
5355    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
5356    arg_types = {
5357        "hour": True,
5358        "min": True,
5359        "sec": True,
5360        "nano": False,
5361        "fractions": False,
5362        "precision": False,
5363    }
5364
5365
5366class DateStrToDate(Func):
5367    pass
5368
5369
5370class DateToDateStr(Func):
5371    pass
5372
5373
5374class DateToDi(Func):
5375    pass
5376
5377
5378# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5379class Date(Func):
5380    arg_types = {"this": False, "zone": False, "expressions": False}
5381    is_var_len_args = True
5382
5383
5384class Day(Func):
5385    pass
5386
5387
5388class Decode(Func):
5389    arg_types = {"this": True, "charset": True, "replace": False}
5390
5391
5392class DiToDate(Func):
5393    pass
5394
5395
5396class Encode(Func):
5397    arg_types = {"this": True, "charset": True}
5398
5399
5400class Exp(Func):
5401    pass
5402
5403
5404# https://docs.snowflake.com/en/sql-reference/functions/flatten
5405class Explode(Func):
5406    arg_types = {"this": True, "expressions": False}
5407    is_var_len_args = True
5408
5409
5410# https://spark.apache.org/docs/latest/api/sql/#inline
5411class Inline(Func):
5412    pass
5413
5414
5415class ExplodeOuter(Explode):
5416    pass
5417
5418
5419class Posexplode(Explode):
5420    pass
5421
5422
5423class PosexplodeOuter(Posexplode, ExplodeOuter):
5424    pass
5425
5426
5427class Unnest(Func, UDTF):
5428    arg_types = {
5429        "expressions": True,
5430        "alias": False,
5431        "offset": False,
5432        "explode_array": False,
5433    }
5434
5435    @property
5436    def selects(self) -> t.List[Expression]:
5437        columns = super().selects
5438        offset = self.args.get("offset")
5439        if offset:
5440            columns = columns + [to_identifier("offset") if offset is True else offset]
5441        return columns
5442
5443
5444class Floor(Func):
5445    arg_types = {"this": True, "decimals": False}
5446
5447
5448class FromBase64(Func):
5449    pass
5450
5451
5452class ToBase64(Func):
5453    pass
5454
5455
5456# https://trino.io/docs/current/functions/datetime.html#from_iso8601_timestamp
5457class FromISO8601Timestamp(Func):
5458    _sql_names = ["FROM_ISO8601_TIMESTAMP"]
5459
5460
5461class GapFill(Func):
5462    arg_types = {
5463        "this": True,
5464        "ts_column": True,
5465        "bucket_width": True,
5466        "partitioning_columns": False,
5467        "value_columns": False,
5468        "origin": False,
5469        "ignore_nulls": False,
5470    }
5471
5472
5473# https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_date_array
5474class GenerateDateArray(Func):
5475    arg_types = {"start": True, "end": True, "step": False}
5476
5477
5478# https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_timestamp_array
5479class GenerateTimestampArray(Func):
5480    arg_types = {"start": True, "end": True, "step": True}
5481
5482
5483class Greatest(Func):
5484    arg_types = {"this": True, "expressions": False}
5485    is_var_len_args = True
5486
5487
5488class GroupConcat(AggFunc):
5489    arg_types = {"this": True, "separator": False}
5490
5491
5492class Hex(Func):
5493    pass
5494
5495
5496class LowerHex(Hex):
5497    pass
5498
5499
5500class Xor(Connector, Func):
5501    arg_types = {"this": False, "expression": False, "expressions": False}
5502
5503
5504class If(Func):
5505    arg_types = {"this": True, "true": True, "false": False}
5506    _sql_names = ["IF", "IIF"]
5507
5508
5509class Nullif(Func):
5510    arg_types = {"this": True, "expression": True}
5511
5512
5513class Initcap(Func):
5514    arg_types = {"this": True, "expression": False}
5515
5516
5517class IsNan(Func):
5518    _sql_names = ["IS_NAN", "ISNAN"]
5519
5520
5521class IsInf(Func):
5522    _sql_names = ["IS_INF", "ISINF"]
5523
5524
5525# https://www.postgresql.org/docs/current/functions-json.html
5526class JSON(Expression):
5527    arg_types = {"this": False, "with": False, "unique": False}
5528
5529
5530class JSONPath(Expression):
5531    arg_types = {"expressions": True, "escape": False}
5532
5533    @property
5534    def output_name(self) -> str:
5535        last_segment = self.expressions[-1].this
5536        return last_segment if isinstance(last_segment, str) else ""
5537
5538
5539class JSONPathPart(Expression):
5540    arg_types = {}
5541
5542
5543class JSONPathFilter(JSONPathPart):
5544    arg_types = {"this": True}
5545
5546
5547class JSONPathKey(JSONPathPart):
5548    arg_types = {"this": True}
5549
5550
5551class JSONPathRecursive(JSONPathPart):
5552    arg_types = {"this": False}
5553
5554
5555class JSONPathRoot(JSONPathPart):
5556    pass
5557
5558
5559class JSONPathScript(JSONPathPart):
5560    arg_types = {"this": True}
5561
5562
5563class JSONPathSlice(JSONPathPart):
5564    arg_types = {"start": False, "end": False, "step": False}
5565
5566
5567class JSONPathSelector(JSONPathPart):
5568    arg_types = {"this": True}
5569
5570
5571class JSONPathSubscript(JSONPathPart):
5572    arg_types = {"this": True}
5573
5574
5575class JSONPathUnion(JSONPathPart):
5576    arg_types = {"expressions": True}
5577
5578
5579class JSONPathWildcard(JSONPathPart):
5580    pass
5581
5582
5583class FormatJson(Expression):
5584    pass
5585
5586
5587class JSONKeyValue(Expression):
5588    arg_types = {"this": True, "expression": True}
5589
5590
5591class JSONObject(Func):
5592    arg_types = {
5593        "expressions": False,
5594        "null_handling": False,
5595        "unique_keys": False,
5596        "return_type": False,
5597        "encoding": False,
5598    }
5599
5600
5601class JSONObjectAgg(AggFunc):
5602    arg_types = {
5603        "expressions": False,
5604        "null_handling": False,
5605        "unique_keys": False,
5606        "return_type": False,
5607        "encoding": False,
5608    }
5609
5610
5611# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5612class JSONArray(Func):
5613    arg_types = {
5614        "expressions": True,
5615        "null_handling": False,
5616        "return_type": False,
5617        "strict": False,
5618    }
5619
5620
5621# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5622class JSONArrayAgg(Func):
5623    arg_types = {
5624        "this": True,
5625        "order": False,
5626        "null_handling": False,
5627        "return_type": False,
5628        "strict": False,
5629    }
5630
5631
5632class JSONExists(Func):
5633    arg_types = {"this": True, "path": True, "passing": False, "on_condition": False}
5634
5635
5636# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5637# Note: parsing of JSON column definitions is currently incomplete.
5638class JSONColumnDef(Expression):
5639    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5640
5641
5642class JSONSchema(Expression):
5643    arg_types = {"expressions": True}
5644
5645
5646# https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-value
5647class JSONValue(Expression):
5648    arg_types = {
5649        "this": True,
5650        "path": True,
5651        "returning": False,
5652        "on_condition": False,
5653    }
5654
5655
5656# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5657class JSONTable(Func):
5658    arg_types = {
5659        "this": True,
5660        "schema": True,
5661        "path": False,
5662        "error_handling": False,
5663        "empty_handling": False,
5664    }
5665
5666
5667# https://docs.snowflake.com/en/sql-reference/functions/object_insert
5668class ObjectInsert(Func):
5669    arg_types = {
5670        "this": True,
5671        "key": True,
5672        "value": True,
5673        "update_flag": False,
5674    }
5675
5676
5677class OpenJSONColumnDef(Expression):
5678    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5679
5680
5681class OpenJSON(Func):
5682    arg_types = {"this": True, "path": False, "expressions": False}
5683
5684
5685class JSONBContains(Binary, Func):
5686    _sql_names = ["JSONB_CONTAINS"]
5687
5688
5689class JSONExtract(Binary, Func):
5690    arg_types = {
5691        "this": True,
5692        "expression": True,
5693        "only_json_types": False,
5694        "expressions": False,
5695        "variant_extract": False,
5696    }
5697    _sql_names = ["JSON_EXTRACT"]
5698    is_var_len_args = True
5699
5700    @property
5701    def output_name(self) -> str:
5702        return self.expression.output_name if not self.expressions else ""
5703
5704
5705class JSONExtractScalar(Binary, Func):
5706    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5707    _sql_names = ["JSON_EXTRACT_SCALAR"]
5708    is_var_len_args = True
5709
5710    @property
5711    def output_name(self) -> str:
5712        return self.expression.output_name
5713
5714
5715class JSONBExtract(Binary, Func):
5716    _sql_names = ["JSONB_EXTRACT"]
5717
5718
5719class JSONBExtractScalar(Binary, Func):
5720    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5721
5722
5723class JSONFormat(Func):
5724    arg_types = {"this": False, "options": False}
5725    _sql_names = ["JSON_FORMAT"]
5726
5727
5728# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5729class JSONArrayContains(Binary, Predicate, Func):
5730    _sql_names = ["JSON_ARRAY_CONTAINS"]
5731
5732
5733class ParseJSON(Func):
5734    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5735    # Snowflake also has TRY_PARSE_JSON, which is represented using `safe`
5736    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5737    arg_types = {"this": True, "expression": False, "safe": False}
5738
5739
5740class Least(Func):
5741    arg_types = {"this": True, "expressions": False}
5742    is_var_len_args = True
5743
5744
5745class Left(Func):
5746    arg_types = {"this": True, "expression": True}
5747
5748
5749class Right(Func):
5750    arg_types = {"this": True, "expression": True}
5751
5752
5753class Length(Func):
5754    arg_types = {"this": True, "binary": False}
5755    _sql_names = ["LENGTH", "LEN"]
5756
5757
5758class Levenshtein(Func):
5759    arg_types = {
5760        "this": True,
5761        "expression": False,
5762        "ins_cost": False,
5763        "del_cost": False,
5764        "sub_cost": False,
5765    }
5766
5767
5768class Ln(Func):
5769    pass
5770
5771
5772class Log(Func):
5773    arg_types = {"this": True, "expression": False}
5774
5775
5776class LogicalOr(AggFunc):
5777    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5778
5779
5780class LogicalAnd(AggFunc):
5781    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5782
5783
5784class Lower(Func):
5785    _sql_names = ["LOWER", "LCASE"]
5786
5787
5788class Map(Func):
5789    arg_types = {"keys": False, "values": False}
5790
5791    @property
5792    def keys(self) -> t.List[Expression]:
5793        keys = self.args.get("keys")
5794        return keys.expressions if keys else []
5795
5796    @property
5797    def values(self) -> t.List[Expression]:
5798        values = self.args.get("values")
5799        return values.expressions if values else []
5800
5801
5802# Represents the MAP {...} syntax in DuckDB - basically convert a struct to a MAP
5803class ToMap(Func):
5804    pass
5805
5806
5807class MapFromEntries(Func):
5808    pass
5809
5810
5811# https://learn.microsoft.com/en-us/sql/t-sql/language-elements/scope-resolution-operator-transact-sql?view=sql-server-ver16
5812class ScopeResolution(Expression):
5813    arg_types = {"this": False, "expression": True}
5814
5815
5816class Stream(Expression):
5817    pass
5818
5819
5820class StarMap(Func):
5821    pass
5822
5823
5824class VarMap(Func):
5825    arg_types = {"keys": True, "values": True}
5826    is_var_len_args = True
5827
5828    @property
5829    def keys(self) -> t.List[Expression]:
5830        return self.args["keys"].expressions
5831
5832    @property
5833    def values(self) -> t.List[Expression]:
5834        return self.args["values"].expressions
5835
5836
5837# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5838class MatchAgainst(Func):
5839    arg_types = {"this": True, "expressions": True, "modifier": False}
5840
5841
5842class Max(AggFunc):
5843    arg_types = {"this": True, "expressions": False}
5844    is_var_len_args = True
5845
5846
5847class MD5(Func):
5848    _sql_names = ["MD5"]
5849
5850
5851# Represents the variant of the MD5 function that returns a binary value
5852class MD5Digest(Func):
5853    _sql_names = ["MD5_DIGEST"]
5854
5855
5856class Min(AggFunc):
5857    arg_types = {"this": True, "expressions": False}
5858    is_var_len_args = True
5859
5860
5861class Month(Func):
5862    pass
5863
5864
5865class AddMonths(Func):
5866    arg_types = {"this": True, "expression": True}
5867
5868
5869class Nvl2(Func):
5870    arg_types = {"this": True, "true": True, "false": False}
5871
5872
5873class Normalize(Func):
5874    arg_types = {"this": True, "form": False}
5875
5876
5877# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5878class Predict(Func):
5879    arg_types = {"this": True, "expression": True, "params_struct": False}
5880
5881
5882class Pow(Binary, Func):
5883    _sql_names = ["POWER", "POW"]
5884
5885
5886class PercentileCont(AggFunc):
5887    arg_types = {"this": True, "expression": False}
5888
5889
5890class PercentileDisc(AggFunc):
5891    arg_types = {"this": True, "expression": False}
5892
5893
5894class Quantile(AggFunc):
5895    arg_types = {"this": True, "quantile": True}
5896
5897
5898class ApproxQuantile(Quantile):
5899    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5900
5901
5902class Quarter(Func):
5903    pass
5904
5905
5906# https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Functions-Expressions-and-Predicates/Arithmetic-Trigonometric-Hyperbolic-Operators/Functions/RANDOM/RANDOM-Function-Syntax
5907# teradata lower and upper bounds
5908class Rand(Func):
5909    _sql_names = ["RAND", "RANDOM"]
5910    arg_types = {"this": False, "lower": False, "upper": False}
5911
5912
5913class Randn(Func):
5914    arg_types = {"this": False}
5915
5916
5917class RangeN(Func):
5918    arg_types = {"this": True, "expressions": True, "each": False}
5919
5920
5921class ReadCSV(Func):
5922    _sql_names = ["READ_CSV"]
5923    is_var_len_args = True
5924    arg_types = {"this": True, "expressions": False}
5925
5926
5927class Reduce(Func):
5928    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5929
5930
5931class RegexpExtract(Func):
5932    arg_types = {
5933        "this": True,
5934        "expression": True,
5935        "position": False,
5936        "occurrence": False,
5937        "parameters": False,
5938        "group": False,
5939    }
5940
5941
5942class RegexpReplace(Func):
5943    arg_types = {
5944        "this": True,
5945        "expression": True,
5946        "replacement": False,
5947        "position": False,
5948        "occurrence": False,
5949        "modifiers": False,
5950    }
5951
5952
5953class RegexpLike(Binary, Func):
5954    arg_types = {"this": True, "expression": True, "flag": False}
5955
5956
5957class RegexpILike(Binary, Func):
5958    arg_types = {"this": True, "expression": True, "flag": False}
5959
5960
5961# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5962# limit is the number of times a pattern is applied
5963class RegexpSplit(Func):
5964    arg_types = {"this": True, "expression": True, "limit": False}
5965
5966
5967class Repeat(Func):
5968    arg_types = {"this": True, "times": True}
5969
5970
5971# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5972# tsql third argument function == trunctaion if not 0
5973class Round(Func):
5974    arg_types = {"this": True, "decimals": False, "truncate": False}
5975
5976
5977class RowNumber(Func):
5978    arg_types: t.Dict[str, t.Any] = {}
5979
5980
5981class SafeDivide(Func):
5982    arg_types = {"this": True, "expression": True}
5983
5984
5985class SHA(Func):
5986    _sql_names = ["SHA", "SHA1"]
5987
5988
5989class SHA2(Func):
5990    _sql_names = ["SHA2"]
5991    arg_types = {"this": True, "length": False}
5992
5993
5994class Sign(Func):
5995    _sql_names = ["SIGN", "SIGNUM"]
5996
5997
5998class SortArray(Func):
5999    arg_types = {"this": True, "asc": False}
6000
6001
6002class Split(Func):
6003    arg_types = {"this": True, "expression": True, "limit": False}
6004
6005
6006# Start may be omitted in the case of postgres
6007# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
6008class Substring(Func):
6009    arg_types = {"this": True, "start": False, "length": False}
6010
6011
6012class StandardHash(Func):
6013    arg_types = {"this": True, "expression": False}
6014
6015
6016class StartsWith(Func):
6017    _sql_names = ["STARTS_WITH", "STARTSWITH"]
6018    arg_types = {"this": True, "expression": True}
6019
6020
6021class StrPosition(Func):
6022    arg_types = {
6023        "this": True,
6024        "substr": True,
6025        "position": False,
6026        "instance": False,
6027    }
6028
6029
6030class StrToDate(Func):
6031    arg_types = {"this": True, "format": False, "safe": False}
6032
6033
6034class StrToTime(Func):
6035    arg_types = {"this": True, "format": True, "zone": False, "safe": False}
6036
6037
6038# Spark allows unix_timestamp()
6039# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
6040class StrToUnix(Func):
6041    arg_types = {"this": False, "format": False}
6042
6043
6044# https://prestodb.io/docs/current/functions/string.html
6045# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
6046class StrToMap(Func):
6047    arg_types = {
6048        "this": True,
6049        "pair_delim": False,
6050        "key_value_delim": False,
6051        "duplicate_resolution_callback": False,
6052    }
6053
6054
6055class NumberToStr(Func):
6056    arg_types = {"this": True, "format": True, "culture": False}
6057
6058
6059class FromBase(Func):
6060    arg_types = {"this": True, "expression": True}
6061
6062
6063class Struct(Func):
6064    arg_types = {"expressions": False}
6065    is_var_len_args = True
6066
6067
6068class StructExtract(Func):
6069    arg_types = {"this": True, "expression": True}
6070
6071
6072# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
6073# https://docs.snowflake.com/en/sql-reference/functions/insert
6074class Stuff(Func):
6075    _sql_names = ["STUFF", "INSERT"]
6076    arg_types = {"this": True, "start": True, "length": True, "expression": True}
6077
6078
6079class Sum(AggFunc):
6080    pass
6081
6082
6083class Sqrt(Func):
6084    pass
6085
6086
6087class Stddev(AggFunc):
6088    _sql_names = ["STDDEV", "STDEV"]
6089
6090
6091class StddevPop(AggFunc):
6092    pass
6093
6094
6095class StddevSamp(AggFunc):
6096    pass
6097
6098
6099# https://cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#time
6100class Time(Func):
6101    arg_types = {"this": False, "zone": False}
6102
6103
6104class TimeToStr(Func):
6105    arg_types = {"this": True, "format": True, "culture": False, "zone": False}
6106
6107
6108class TimeToTimeStr(Func):
6109    pass
6110
6111
6112class TimeToUnix(Func):
6113    pass
6114
6115
6116class TimeStrToDate(Func):
6117    pass
6118
6119
6120class TimeStrToTime(Func):
6121    arg_types = {"this": True, "zone": False}
6122
6123
6124class TimeStrToUnix(Func):
6125    pass
6126
6127
6128class Trim(Func):
6129    arg_types = {
6130        "this": True,
6131        "expression": False,
6132        "position": False,
6133        "collation": False,
6134    }
6135
6136
6137class TsOrDsAdd(Func, TimeUnit):
6138    # return_type is used to correctly cast the arguments of this expression when transpiling it
6139    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
6140
6141    @property
6142    def return_type(self) -> DataType:
6143        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
6144
6145
6146class TsOrDsDiff(Func, TimeUnit):
6147    arg_types = {"this": True, "expression": True, "unit": False}
6148
6149
6150class TsOrDsToDateStr(Func):
6151    pass
6152
6153
6154class TsOrDsToDate(Func):
6155    arg_types = {"this": True, "format": False, "safe": False}
6156
6157
6158class TsOrDsToTime(Func):
6159    pass
6160
6161
6162class TsOrDsToTimestamp(Func):
6163    pass
6164
6165
6166class TsOrDiToDi(Func):
6167    pass
6168
6169
6170class Unhex(Func):
6171    pass
6172
6173
6174# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
6175class UnixDate(Func):
6176    pass
6177
6178
6179class UnixToStr(Func):
6180    arg_types = {"this": True, "format": False}
6181
6182
6183# https://prestodb.io/docs/current/functions/datetime.html
6184# presto has weird zone/hours/minutes
6185class UnixToTime(Func):
6186    arg_types = {
6187        "this": True,
6188        "scale": False,
6189        "zone": False,
6190        "hours": False,
6191        "minutes": False,
6192        "format": False,
6193    }
6194
6195    SECONDS = Literal.number(0)
6196    DECIS = Literal.number(1)
6197    CENTIS = Literal.number(2)
6198    MILLIS = Literal.number(3)
6199    DECIMILLIS = Literal.number(4)
6200    CENTIMILLIS = Literal.number(5)
6201    MICROS = Literal.number(6)
6202    DECIMICROS = Literal.number(7)
6203    CENTIMICROS = Literal.number(8)
6204    NANOS = Literal.number(9)
6205
6206
6207class UnixToTimeStr(Func):
6208    pass
6209
6210
6211class UnpackColumns(Func):
6212    pass
6213
6214
6215class Uuid(Func):
6216    _sql_names = ["UUID", "GEN_RANDOM_UUID", "GENERATE_UUID", "UUID_STRING"]
6217
6218    arg_types = {"this": False, "name": False}
6219
6220
6221class TimestampFromParts(Func):
6222    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
6223    arg_types = {
6224        "year": True,
6225        "month": True,
6226        "day": True,
6227        "hour": True,
6228        "min": True,
6229        "sec": True,
6230        "nano": False,
6231        "zone": False,
6232        "milli": False,
6233    }
6234
6235
6236class Upper(Func):
6237    _sql_names = ["UPPER", "UCASE"]
6238
6239
6240class Corr(Binary, AggFunc):
6241    pass
6242
6243
6244class Variance(AggFunc):
6245    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
6246
6247
6248class VariancePop(AggFunc):
6249    _sql_names = ["VARIANCE_POP", "VAR_POP"]
6250
6251
6252class CovarSamp(Binary, AggFunc):
6253    pass
6254
6255
6256class CovarPop(Binary, AggFunc):
6257    pass
6258
6259
6260class Week(Func):
6261    arg_types = {"this": True, "mode": False}
6262
6263
6264class XMLTable(Func):
6265    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
6266
6267
6268class Year(Func):
6269    pass
6270
6271
6272class Use(Expression):
6273    arg_types = {"this": True, "kind": False}
6274
6275
6276class Merge(Expression):
6277    arg_types = {
6278        "this": True,
6279        "using": True,
6280        "on": True,
6281        "expressions": True,
6282        "with": False,
6283        "returning": False,
6284    }
6285
6286
6287class When(Func):
6288    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
6289
6290
6291# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
6292# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
6293class NextValueFor(Func):
6294    arg_types = {"this": True, "order": False}
6295
6296
6297# Refers to a trailing semi-colon. This is only used to preserve trailing comments
6298# select 1; -- my comment
6299class Semicolon(Expression):
6300    arg_types = {}
6301
6302
6303def _norm_arg(arg):
6304    return arg.lower() if type(arg) is str else arg
6305
6306
6307ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
6308FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
6309
6310JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
6311
6312PERCENTILES = (PercentileCont, PercentileDisc)
6313
6314
6315# Helpers
6316@t.overload
6317def maybe_parse(
6318    sql_or_expression: ExpOrStr,
6319    *,
6320    into: t.Type[E],
6321    dialect: DialectType = None,
6322    prefix: t.Optional[str] = None,
6323    copy: bool = False,
6324    **opts,
6325) -> E: ...
6326
6327
6328@t.overload
6329def maybe_parse(
6330    sql_or_expression: str | E,
6331    *,
6332    into: t.Optional[IntoType] = None,
6333    dialect: DialectType = None,
6334    prefix: t.Optional[str] = None,
6335    copy: bool = False,
6336    **opts,
6337) -> E: ...
6338
6339
6340def maybe_parse(
6341    sql_or_expression: ExpOrStr,
6342    *,
6343    into: t.Optional[IntoType] = None,
6344    dialect: DialectType = None,
6345    prefix: t.Optional[str] = None,
6346    copy: bool = False,
6347    **opts,
6348) -> Expression:
6349    """Gracefully handle a possible string or expression.
6350
6351    Example:
6352        >>> maybe_parse("1")
6353        Literal(this=1, is_string=False)
6354        >>> maybe_parse(to_identifier("x"))
6355        Identifier(this=x, quoted=False)
6356
6357    Args:
6358        sql_or_expression: the SQL code string or an expression
6359        into: the SQLGlot Expression to parse into
6360        dialect: the dialect used to parse the input expressions (in the case that an
6361            input expression is a SQL string).
6362        prefix: a string to prefix the sql with before it gets parsed
6363            (automatically includes a space)
6364        copy: whether to copy the expression.
6365        **opts: other options to use to parse the input expressions (again, in the case
6366            that an input expression is a SQL string).
6367
6368    Returns:
6369        Expression: the parsed or given expression.
6370    """
6371    if isinstance(sql_or_expression, Expression):
6372        if copy:
6373            return sql_or_expression.copy()
6374        return sql_or_expression
6375
6376    if sql_or_expression is None:
6377        raise ParseError("SQL cannot be None")
6378
6379    import sqlglot
6380
6381    sql = str(sql_or_expression)
6382    if prefix:
6383        sql = f"{prefix} {sql}"
6384
6385    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
6386
6387
6388@t.overload
6389def maybe_copy(instance: None, copy: bool = True) -> None: ...
6390
6391
6392@t.overload
6393def maybe_copy(instance: E, copy: bool = True) -> E: ...
6394
6395
6396def maybe_copy(instance, copy=True):
6397    return instance.copy() if copy and instance else instance
6398
6399
6400def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
6401    """Generate a textual representation of an Expression tree"""
6402    indent = "\n" + ("  " * (level + 1))
6403    delim = f",{indent}"
6404
6405    if isinstance(node, Expression):
6406        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
6407
6408        if (node.type or verbose) and not isinstance(node, DataType):
6409            args["_type"] = node.type
6410        if node.comments or verbose:
6411            args["_comments"] = node.comments
6412
6413        if verbose:
6414            args["_id"] = id(node)
6415
6416        # Inline leaves for a more compact representation
6417        if node.is_leaf():
6418            indent = ""
6419            delim = ", "
6420
6421        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
6422        return f"{node.__class__.__name__}({indent}{items})"
6423
6424    if isinstance(node, list):
6425        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
6426        items = f"{indent}{items}" if items else ""
6427        return f"[{items}]"
6428
6429    # Indent multiline strings to match the current level
6430    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
6431
6432
6433def _is_wrong_expression(expression, into):
6434    return isinstance(expression, Expression) and not isinstance(expression, into)
6435
6436
6437def _apply_builder(
6438    expression,
6439    instance,
6440    arg,
6441    copy=True,
6442    prefix=None,
6443    into=None,
6444    dialect=None,
6445    into_arg="this",
6446    **opts,
6447):
6448    if _is_wrong_expression(expression, into):
6449        expression = into(**{into_arg: expression})
6450    instance = maybe_copy(instance, copy)
6451    expression = maybe_parse(
6452        sql_or_expression=expression,
6453        prefix=prefix,
6454        into=into,
6455        dialect=dialect,
6456        **opts,
6457    )
6458    instance.set(arg, expression)
6459    return instance
6460
6461
6462def _apply_child_list_builder(
6463    *expressions,
6464    instance,
6465    arg,
6466    append=True,
6467    copy=True,
6468    prefix=None,
6469    into=None,
6470    dialect=None,
6471    properties=None,
6472    **opts,
6473):
6474    instance = maybe_copy(instance, copy)
6475    parsed = []
6476    properties = {} if properties is None else properties
6477
6478    for expression in expressions:
6479        if expression is not None:
6480            if _is_wrong_expression(expression, into):
6481                expression = into(expressions=[expression])
6482
6483            expression = maybe_parse(
6484                expression,
6485                into=into,
6486                dialect=dialect,
6487                prefix=prefix,
6488                **opts,
6489            )
6490            for k, v in expression.args.items():
6491                if k == "expressions":
6492                    parsed.extend(v)
6493                else:
6494                    properties[k] = v
6495
6496    existing = instance.args.get(arg)
6497    if append and existing:
6498        parsed = existing.expressions + parsed
6499
6500    child = into(expressions=parsed)
6501    for k, v in properties.items():
6502        child.set(k, v)
6503    instance.set(arg, child)
6504
6505    return instance
6506
6507
6508def _apply_list_builder(
6509    *expressions,
6510    instance,
6511    arg,
6512    append=True,
6513    copy=True,
6514    prefix=None,
6515    into=None,
6516    dialect=None,
6517    **opts,
6518):
6519    inst = maybe_copy(instance, copy)
6520
6521    expressions = [
6522        maybe_parse(
6523            sql_or_expression=expression,
6524            into=into,
6525            prefix=prefix,
6526            dialect=dialect,
6527            **opts,
6528        )
6529        for expression in expressions
6530        if expression is not None
6531    ]
6532
6533    existing_expressions = inst.args.get(arg)
6534    if append and existing_expressions:
6535        expressions = existing_expressions + expressions
6536
6537    inst.set(arg, expressions)
6538    return inst
6539
6540
6541def _apply_conjunction_builder(
6542    *expressions,
6543    instance,
6544    arg,
6545    into=None,
6546    append=True,
6547    copy=True,
6548    dialect=None,
6549    **opts,
6550):
6551    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6552    if not expressions:
6553        return instance
6554
6555    inst = maybe_copy(instance, copy)
6556
6557    existing = inst.args.get(arg)
6558    if append and existing is not None:
6559        expressions = [existing.this if into else existing] + list(expressions)
6560
6561    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6562
6563    inst.set(arg, into(this=node) if into else node)
6564    return inst
6565
6566
6567def _apply_cte_builder(
6568    instance: E,
6569    alias: ExpOrStr,
6570    as_: ExpOrStr,
6571    recursive: t.Optional[bool] = None,
6572    materialized: t.Optional[bool] = None,
6573    append: bool = True,
6574    dialect: DialectType = None,
6575    copy: bool = True,
6576    **opts,
6577) -> E:
6578    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6579    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6580    cte = CTE(this=as_expression, alias=alias_expression, materialized=materialized)
6581    return _apply_child_list_builder(
6582        cte,
6583        instance=instance,
6584        arg="with",
6585        append=append,
6586        copy=copy,
6587        into=With,
6588        properties={"recursive": recursive or False},
6589    )
6590
6591
6592def _combine(
6593    expressions: t.Sequence[t.Optional[ExpOrStr]],
6594    operator: t.Type[Connector],
6595    dialect: DialectType = None,
6596    copy: bool = True,
6597    **opts,
6598) -> Expression:
6599    conditions = [
6600        condition(expression, dialect=dialect, copy=copy, **opts)
6601        for expression in expressions
6602        if expression is not None
6603    ]
6604
6605    this, *rest = conditions
6606    if rest:
6607        this = _wrap(this, Connector)
6608    for expression in rest:
6609        this = operator(this=this, expression=_wrap(expression, Connector))
6610
6611    return this
6612
6613
6614def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6615    return Paren(this=expression) if isinstance(expression, kind) else expression
6616
6617
6618def union(
6619    left: ExpOrStr,
6620    right: ExpOrStr,
6621    distinct: bool = True,
6622    dialect: DialectType = None,
6623    copy: bool = True,
6624    **opts,
6625) -> Union:
6626    """
6627    Initializes a syntax tree from one UNION expression.
6628
6629    Example:
6630        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6631        'SELECT * FROM foo UNION SELECT * FROM bla'
6632
6633    Args:
6634        left: the SQL code string corresponding to the left-hand side.
6635            If an `Expression` instance is passed, it will be used as-is.
6636        right: the SQL code string corresponding to the right-hand side.
6637            If an `Expression` instance is passed, it will be used as-is.
6638        distinct: set the DISTINCT flag if and only if this is true.
6639        dialect: the dialect used to parse the input expression.
6640        copy: whether to copy the expression.
6641        opts: other options to use to parse the input expressions.
6642
6643    Returns:
6644        The new Union instance.
6645    """
6646    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6647    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6648
6649    return Union(this=left, expression=right, distinct=distinct)
6650
6651
6652def intersect(
6653    left: ExpOrStr,
6654    right: ExpOrStr,
6655    distinct: bool = True,
6656    dialect: DialectType = None,
6657    copy: bool = True,
6658    **opts,
6659) -> Intersect:
6660    """
6661    Initializes a syntax tree from one INTERSECT expression.
6662
6663    Example:
6664        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6665        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6666
6667    Args:
6668        left: the SQL code string corresponding to the left-hand side.
6669            If an `Expression` instance is passed, it will be used as-is.
6670        right: the SQL code string corresponding to the right-hand side.
6671            If an `Expression` instance is passed, it will be used as-is.
6672        distinct: set the DISTINCT flag if and only if this is true.
6673        dialect: the dialect used to parse the input expression.
6674        copy: whether to copy the expression.
6675        opts: other options to use to parse the input expressions.
6676
6677    Returns:
6678        The new Intersect instance.
6679    """
6680    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6681    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6682
6683    return Intersect(this=left, expression=right, distinct=distinct)
6684
6685
6686def except_(
6687    left: ExpOrStr,
6688    right: ExpOrStr,
6689    distinct: bool = True,
6690    dialect: DialectType = None,
6691    copy: bool = True,
6692    **opts,
6693) -> Except:
6694    """
6695    Initializes a syntax tree from one EXCEPT expression.
6696
6697    Example:
6698        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6699        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6700
6701    Args:
6702        left: the SQL code string corresponding to the left-hand side.
6703            If an `Expression` instance is passed, it will be used as-is.
6704        right: the SQL code string corresponding to the right-hand side.
6705            If an `Expression` instance is passed, it will be used as-is.
6706        distinct: set the DISTINCT flag if and only if this is true.
6707        dialect: the dialect used to parse the input expression.
6708        copy: whether to copy the expression.
6709        opts: other options to use to parse the input expressions.
6710
6711    Returns:
6712        The new Except instance.
6713    """
6714    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6715    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6716
6717    return Except(this=left, expression=right, distinct=distinct)
6718
6719
6720def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6721    """
6722    Initializes a syntax tree from one or multiple SELECT expressions.
6723
6724    Example:
6725        >>> select("col1", "col2").from_("tbl").sql()
6726        'SELECT col1, col2 FROM tbl'
6727
6728    Args:
6729        *expressions: the SQL code string to parse as the expressions of a
6730            SELECT statement. If an Expression instance is passed, this is used as-is.
6731        dialect: the dialect used to parse the input expressions (in the case that an
6732            input expression is a SQL string).
6733        **opts: other options to use to parse the input expressions (again, in the case
6734            that an input expression is a SQL string).
6735
6736    Returns:
6737        Select: the syntax tree for the SELECT statement.
6738    """
6739    return Select().select(*expressions, dialect=dialect, **opts)
6740
6741
6742def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6743    """
6744    Initializes a syntax tree from a FROM expression.
6745
6746    Example:
6747        >>> from_("tbl").select("col1", "col2").sql()
6748        'SELECT col1, col2 FROM tbl'
6749
6750    Args:
6751        *expression: the SQL code string to parse as the FROM expressions of a
6752            SELECT statement. If an Expression instance is passed, this is used as-is.
6753        dialect: the dialect used to parse the input expression (in the case that the
6754            input expression is a SQL string).
6755        **opts: other options to use to parse the input expressions (again, in the case
6756            that the input expression is a SQL string).
6757
6758    Returns:
6759        Select: the syntax tree for the SELECT statement.
6760    """
6761    return Select().from_(expression, dialect=dialect, **opts)
6762
6763
6764def update(
6765    table: str | Table,
6766    properties: dict,
6767    where: t.Optional[ExpOrStr] = None,
6768    from_: t.Optional[ExpOrStr] = None,
6769    dialect: DialectType = None,
6770    **opts,
6771) -> Update:
6772    """
6773    Creates an update statement.
6774
6775    Example:
6776        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6777        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6778
6779    Args:
6780        *properties: dictionary of properties to set which are
6781            auto converted to sql objects eg None -> NULL
6782        where: sql conditional parsed into a WHERE statement
6783        from_: sql statement parsed into a FROM statement
6784        dialect: the dialect used to parse the input expressions.
6785        **opts: other options to use to parse the input expressions.
6786
6787    Returns:
6788        Update: the syntax tree for the UPDATE statement.
6789    """
6790    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6791    update_expr.set(
6792        "expressions",
6793        [
6794            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6795            for k, v in properties.items()
6796        ],
6797    )
6798    if from_:
6799        update_expr.set(
6800            "from",
6801            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6802        )
6803    if isinstance(where, Condition):
6804        where = Where(this=where)
6805    if where:
6806        update_expr.set(
6807            "where",
6808            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6809        )
6810    return update_expr
6811
6812
6813def delete(
6814    table: ExpOrStr,
6815    where: t.Optional[ExpOrStr] = None,
6816    returning: t.Optional[ExpOrStr] = None,
6817    dialect: DialectType = None,
6818    **opts,
6819) -> Delete:
6820    """
6821    Builds a delete statement.
6822
6823    Example:
6824        >>> delete("my_table", where="id > 1").sql()
6825        'DELETE FROM my_table WHERE id > 1'
6826
6827    Args:
6828        where: sql conditional parsed into a WHERE statement
6829        returning: sql conditional parsed into a RETURNING statement
6830        dialect: the dialect used to parse the input expressions.
6831        **opts: other options to use to parse the input expressions.
6832
6833    Returns:
6834        Delete: the syntax tree for the DELETE statement.
6835    """
6836    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6837    if where:
6838        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6839    if returning:
6840        delete_expr = t.cast(
6841            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6842        )
6843    return delete_expr
6844
6845
6846def insert(
6847    expression: ExpOrStr,
6848    into: ExpOrStr,
6849    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6850    overwrite: t.Optional[bool] = None,
6851    returning: t.Optional[ExpOrStr] = None,
6852    dialect: DialectType = None,
6853    copy: bool = True,
6854    **opts,
6855) -> Insert:
6856    """
6857    Builds an INSERT statement.
6858
6859    Example:
6860        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6861        'INSERT INTO tbl VALUES (1, 2, 3)'
6862
6863    Args:
6864        expression: the sql string or expression of the INSERT statement
6865        into: the tbl to insert data to.
6866        columns: optionally the table's column names.
6867        overwrite: whether to INSERT OVERWRITE or not.
6868        returning: sql conditional parsed into a RETURNING statement
6869        dialect: the dialect used to parse the input expressions.
6870        copy: whether to copy the expression.
6871        **opts: other options to use to parse the input expressions.
6872
6873    Returns:
6874        Insert: the syntax tree for the INSERT statement.
6875    """
6876    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6877    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6878
6879    if columns:
6880        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6881
6882    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6883
6884    if returning:
6885        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6886
6887    return insert
6888
6889
6890def merge(
6891    *when_exprs: ExpOrStr,
6892    into: ExpOrStr,
6893    using: ExpOrStr,
6894    on: ExpOrStr,
6895    dialect: DialectType = None,
6896    copy: bool = True,
6897    **opts,
6898) -> Merge:
6899    """
6900    Builds a MERGE statement.
6901
6902    Example:
6903        >>> merge("WHEN MATCHED THEN UPDATE SET col1 = source_table.col1",
6904        ...       "WHEN NOT MATCHED THEN INSERT (col1) VALUES (source_table.col1)",
6905        ...       into="my_table",
6906        ...       using="source_table",
6907        ...       on="my_table.id = source_table.id").sql()
6908        'MERGE INTO my_table USING source_table ON my_table.id = source_table.id WHEN MATCHED THEN UPDATE SET col1 = source_table.col1 WHEN NOT MATCHED THEN INSERT (col1) VALUES (source_table.col1)'
6909
6910    Args:
6911        *when_exprs: The WHEN clauses specifying actions for matched and unmatched rows.
6912        into: The target table to merge data into.
6913        using: The source table to merge data from.
6914        on: The join condition for the merge.
6915        dialect: The dialect used to parse the input expressions.
6916        copy: Whether to copy the expression.
6917        **opts: Other options to use to parse the input expressions.
6918
6919    Returns:
6920        Merge: The syntax tree for the MERGE statement.
6921    """
6922    return Merge(
6923        this=maybe_parse(into, dialect=dialect, copy=copy, **opts),
6924        using=maybe_parse(using, dialect=dialect, copy=copy, **opts),
6925        on=maybe_parse(on, dialect=dialect, copy=copy, **opts),
6926        expressions=[
6927            maybe_parse(when_expr, dialect=dialect, copy=copy, into=When, **opts)
6928            for when_expr in when_exprs
6929        ],
6930    )
6931
6932
6933def condition(
6934    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6935) -> Condition:
6936    """
6937    Initialize a logical condition expression.
6938
6939    Example:
6940        >>> condition("x=1").sql()
6941        'x = 1'
6942
6943        This is helpful for composing larger logical syntax trees:
6944        >>> where = condition("x=1")
6945        >>> where = where.and_("y=1")
6946        >>> Select().from_("tbl").select("*").where(where).sql()
6947        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6948
6949    Args:
6950        *expression: the SQL code string to parse.
6951            If an Expression instance is passed, this is used as-is.
6952        dialect: the dialect used to parse the input expression (in the case that the
6953            input expression is a SQL string).
6954        copy: Whether to copy `expression` (only applies to expressions).
6955        **opts: other options to use to parse the input expressions (again, in the case
6956            that the input expression is a SQL string).
6957
6958    Returns:
6959        The new Condition instance
6960    """
6961    return maybe_parse(
6962        expression,
6963        into=Condition,
6964        dialect=dialect,
6965        copy=copy,
6966        **opts,
6967    )
6968
6969
6970def and_(
6971    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6972) -> Condition:
6973    """
6974    Combine multiple conditions with an AND logical operator.
6975
6976    Example:
6977        >>> and_("x=1", and_("y=1", "z=1")).sql()
6978        'x = 1 AND (y = 1 AND z = 1)'
6979
6980    Args:
6981        *expressions: the SQL code strings to parse.
6982            If an Expression instance is passed, this is used as-is.
6983        dialect: the dialect used to parse the input expression.
6984        copy: whether to copy `expressions` (only applies to Expressions).
6985        **opts: other options to use to parse the input expressions.
6986
6987    Returns:
6988        The new condition
6989    """
6990    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6991
6992
6993def or_(
6994    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6995) -> Condition:
6996    """
6997    Combine multiple conditions with an OR logical operator.
6998
6999    Example:
7000        >>> or_("x=1", or_("y=1", "z=1")).sql()
7001        'x = 1 OR (y = 1 OR z = 1)'
7002
7003    Args:
7004        *expressions: the SQL code strings to parse.
7005            If an Expression instance is passed, this is used as-is.
7006        dialect: the dialect used to parse the input expression.
7007        copy: whether to copy `expressions` (only applies to Expressions).
7008        **opts: other options to use to parse the input expressions.
7009
7010    Returns:
7011        The new condition
7012    """
7013    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
7014
7015
7016def xor(
7017    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
7018) -> Condition:
7019    """
7020    Combine multiple conditions with an XOR logical operator.
7021
7022    Example:
7023        >>> xor("x=1", xor("y=1", "z=1")).sql()
7024        'x = 1 XOR (y = 1 XOR z = 1)'
7025
7026    Args:
7027        *expressions: the SQL code strings to parse.
7028            If an Expression instance is passed, this is used as-is.
7029        dialect: the dialect used to parse the input expression.
7030        copy: whether to copy `expressions` (only applies to Expressions).
7031        **opts: other options to use to parse the input expressions.
7032
7033    Returns:
7034        The new condition
7035    """
7036    return t.cast(Condition, _combine(expressions, Xor, dialect, copy=copy, **opts))
7037
7038
7039def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
7040    """
7041    Wrap a condition with a NOT operator.
7042
7043    Example:
7044        >>> not_("this_suit='black'").sql()
7045        "NOT this_suit = 'black'"
7046
7047    Args:
7048        expression: the SQL code string to parse.
7049            If an Expression instance is passed, this is used as-is.
7050        dialect: the dialect used to parse the input expression.
7051        copy: whether to copy the expression or not.
7052        **opts: other options to use to parse the input expressions.
7053
7054    Returns:
7055        The new condition.
7056    """
7057    this = condition(
7058        expression,
7059        dialect=dialect,
7060        copy=copy,
7061        **opts,
7062    )
7063    return Not(this=_wrap(this, Connector))
7064
7065
7066def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
7067    """
7068    Wrap an expression in parentheses.
7069
7070    Example:
7071        >>> paren("5 + 3").sql()
7072        '(5 + 3)'
7073
7074    Args:
7075        expression: the SQL code string to parse.
7076            If an Expression instance is passed, this is used as-is.
7077        copy: whether to copy the expression or not.
7078
7079    Returns:
7080        The wrapped expression.
7081    """
7082    return Paren(this=maybe_parse(expression, copy=copy))
7083
7084
7085SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
7086
7087
7088@t.overload
7089def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
7090
7091
7092@t.overload
7093def to_identifier(
7094    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
7095) -> Identifier: ...
7096
7097
7098def to_identifier(name, quoted=None, copy=True):
7099    """Builds an identifier.
7100
7101    Args:
7102        name: The name to turn into an identifier.
7103        quoted: Whether to force quote the identifier.
7104        copy: Whether to copy name if it's an Identifier.
7105
7106    Returns:
7107        The identifier ast node.
7108    """
7109
7110    if name is None:
7111        return None
7112
7113    if isinstance(name, Identifier):
7114        identifier = maybe_copy(name, copy)
7115    elif isinstance(name, str):
7116        identifier = Identifier(
7117            this=name,
7118            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
7119        )
7120    else:
7121        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
7122    return identifier
7123
7124
7125def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
7126    """
7127    Parses a given string into an identifier.
7128
7129    Args:
7130        name: The name to parse into an identifier.
7131        dialect: The dialect to parse against.
7132
7133    Returns:
7134        The identifier ast node.
7135    """
7136    try:
7137        expression = maybe_parse(name, dialect=dialect, into=Identifier)
7138    except (ParseError, TokenError):
7139        expression = to_identifier(name)
7140
7141    return expression
7142
7143
7144INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
7145
7146
7147def to_interval(interval: str | Literal) -> Interval:
7148    """Builds an interval expression from a string like '1 day' or '5 months'."""
7149    if isinstance(interval, Literal):
7150        if not interval.is_string:
7151            raise ValueError("Invalid interval string.")
7152
7153        interval = interval.this
7154
7155    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
7156
7157    if not interval_parts:
7158        raise ValueError("Invalid interval string.")
7159
7160    return Interval(
7161        this=Literal.string(interval_parts.group(1)),
7162        unit=Var(this=interval_parts.group(2).upper()),
7163    )
7164
7165
7166def to_table(
7167    sql_path: str | Table, dialect: DialectType = None, copy: bool = True, **kwargs
7168) -> Table:
7169    """
7170    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
7171    If a table is passed in then that table is returned.
7172
7173    Args:
7174        sql_path: a `[catalog].[schema].[table]` string.
7175        dialect: the source dialect according to which the table name will be parsed.
7176        copy: Whether to copy a table if it is passed in.
7177        kwargs: the kwargs to instantiate the resulting `Table` expression with.
7178
7179    Returns:
7180        A table expression.
7181    """
7182    if isinstance(sql_path, Table):
7183        return maybe_copy(sql_path, copy=copy)
7184
7185    table = maybe_parse(sql_path, into=Table, dialect=dialect)
7186
7187    for k, v in kwargs.items():
7188        table.set(k, v)
7189
7190    return table
7191
7192
7193def to_column(
7194    sql_path: str | Column,
7195    quoted: t.Optional[bool] = None,
7196    dialect: DialectType = None,
7197    copy: bool = True,
7198    **kwargs,
7199) -> Column:
7200    """
7201    Create a column from a `[table].[column]` sql path. Table is optional.
7202    If a column is passed in then that column is returned.
7203
7204    Args:
7205        sql_path: a `[table].[column]` string.
7206        quoted: Whether or not to force quote identifiers.
7207        dialect: the source dialect according to which the column name will be parsed.
7208        copy: Whether to copy a column if it is passed in.
7209        kwargs: the kwargs to instantiate the resulting `Column` expression with.
7210
7211    Returns:
7212        A column expression.
7213    """
7214    if isinstance(sql_path, Column):
7215        return maybe_copy(sql_path, copy=copy)
7216
7217    try:
7218        col = maybe_parse(sql_path, into=Column, dialect=dialect)
7219    except ParseError:
7220        return column(*reversed(sql_path.split(".")), quoted=quoted, **kwargs)
7221
7222    for k, v in kwargs.items():
7223        col.set(k, v)
7224
7225    if quoted:
7226        for i in col.find_all(Identifier):
7227            i.set("quoted", True)
7228
7229    return col
7230
7231
7232def alias_(
7233    expression: ExpOrStr,
7234    alias: t.Optional[str | Identifier],
7235    table: bool | t.Sequence[str | Identifier] = False,
7236    quoted: t.Optional[bool] = None,
7237    dialect: DialectType = None,
7238    copy: bool = True,
7239    **opts,
7240):
7241    """Create an Alias expression.
7242
7243    Example:
7244        >>> alias_('foo', 'bar').sql()
7245        'foo AS bar'
7246
7247        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
7248        '(SELECT 1, 2) AS bar(a, b)'
7249
7250    Args:
7251        expression: the SQL code strings to parse.
7252            If an Expression instance is passed, this is used as-is.
7253        alias: the alias name to use. If the name has
7254            special characters it is quoted.
7255        table: Whether to create a table alias, can also be a list of columns.
7256        quoted: whether to quote the alias
7257        dialect: the dialect used to parse the input expression.
7258        copy: Whether to copy the expression.
7259        **opts: other options to use to parse the input expressions.
7260
7261    Returns:
7262        Alias: the aliased expression
7263    """
7264    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
7265    alias = to_identifier(alias, quoted=quoted)
7266
7267    if table:
7268        table_alias = TableAlias(this=alias)
7269        exp.set("alias", table_alias)
7270
7271        if not isinstance(table, bool):
7272            for column in table:
7273                table_alias.append("columns", to_identifier(column, quoted=quoted))
7274
7275        return exp
7276
7277    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
7278    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
7279    # for the complete Window expression.
7280    #
7281    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
7282
7283    if "alias" in exp.arg_types and not isinstance(exp, Window):
7284        exp.set("alias", alias)
7285        return exp
7286    return Alias(this=exp, alias=alias)
7287
7288
7289def subquery(
7290    expression: ExpOrStr,
7291    alias: t.Optional[Identifier | str] = None,
7292    dialect: DialectType = None,
7293    **opts,
7294) -> Select:
7295    """
7296    Build a subquery expression that's selected from.
7297
7298    Example:
7299        >>> subquery('select x from tbl', 'bar').select('x').sql()
7300        'SELECT x FROM (SELECT x FROM tbl) AS bar'
7301
7302    Args:
7303        expression: the SQL code strings to parse.
7304            If an Expression instance is passed, this is used as-is.
7305        alias: the alias name to use.
7306        dialect: the dialect used to parse the input expression.
7307        **opts: other options to use to parse the input expressions.
7308
7309    Returns:
7310        A new Select instance with the subquery expression included.
7311    """
7312
7313    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias, **opts)
7314    return Select().from_(expression, dialect=dialect, **opts)
7315
7316
7317@t.overload
7318def column(
7319    col: str | Identifier,
7320    table: t.Optional[str | Identifier] = None,
7321    db: t.Optional[str | Identifier] = None,
7322    catalog: t.Optional[str | Identifier] = None,
7323    *,
7324    fields: t.Collection[t.Union[str, Identifier]],
7325    quoted: t.Optional[bool] = None,
7326    copy: bool = True,
7327) -> Dot:
7328    pass
7329
7330
7331@t.overload
7332def column(
7333    col: str | Identifier,
7334    table: t.Optional[str | Identifier] = None,
7335    db: t.Optional[str | Identifier] = None,
7336    catalog: t.Optional[str | Identifier] = None,
7337    *,
7338    fields: Lit[None] = None,
7339    quoted: t.Optional[bool] = None,
7340    copy: bool = True,
7341) -> Column:
7342    pass
7343
7344
7345def column(
7346    col,
7347    table=None,
7348    db=None,
7349    catalog=None,
7350    *,
7351    fields=None,
7352    quoted=None,
7353    copy=True,
7354):
7355    """
7356    Build a Column.
7357
7358    Args:
7359        col: Column name.
7360        table: Table name.
7361        db: Database name.
7362        catalog: Catalog name.
7363        fields: Additional fields using dots.
7364        quoted: Whether to force quotes on the column's identifiers.
7365        copy: Whether to copy identifiers if passed in.
7366
7367    Returns:
7368        The new Column instance.
7369    """
7370    this = Column(
7371        this=to_identifier(col, quoted=quoted, copy=copy),
7372        table=to_identifier(table, quoted=quoted, copy=copy),
7373        db=to_identifier(db, quoted=quoted, copy=copy),
7374        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
7375    )
7376
7377    if fields:
7378        this = Dot.build(
7379            (this, *(to_identifier(field, quoted=quoted, copy=copy) for field in fields))
7380        )
7381    return this
7382
7383
7384def cast(
7385    expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, dialect: DialectType = None, **opts
7386) -> Cast:
7387    """Cast an expression to a data type.
7388
7389    Example:
7390        >>> cast('x + 1', 'int').sql()
7391        'CAST(x + 1 AS INT)'
7392
7393    Args:
7394        expression: The expression to cast.
7395        to: The datatype to cast to.
7396        copy: Whether to copy the supplied expressions.
7397        dialect: The target dialect. This is used to prevent a re-cast in the following scenario:
7398            - The expression to be cast is already a exp.Cast expression
7399            - The existing cast is to a type that is logically equivalent to new type
7400
7401            For example, if :expression='CAST(x as DATETIME)' and :to=Type.TIMESTAMP,
7402            but in the target dialect DATETIME is mapped to TIMESTAMP, then we will NOT return `CAST(x (as DATETIME) as TIMESTAMP)`
7403            and instead just return the original expression `CAST(x as DATETIME)`.
7404
7405            This is to prevent it being output as a double cast `CAST(x (as TIMESTAMP) as TIMESTAMP)` once the DATETIME -> TIMESTAMP
7406            mapping is applied in the target dialect generator.
7407
7408    Returns:
7409        The new Cast instance.
7410    """
7411    expr = maybe_parse(expression, copy=copy, dialect=dialect, **opts)
7412    data_type = DataType.build(to, copy=copy, dialect=dialect, **opts)
7413
7414    # dont re-cast if the expression is already a cast to the correct type
7415    if isinstance(expr, Cast):
7416        from sqlglot.dialects.dialect import Dialect
7417
7418        target_dialect = Dialect.get_or_raise(dialect)
7419        type_mapping = target_dialect.generator_class.TYPE_MAPPING
7420
7421        existing_cast_type: DataType.Type = expr.to.this
7422        new_cast_type: DataType.Type = data_type.this
7423        types_are_equivalent = type_mapping.get(
7424            existing_cast_type, existing_cast_type
7425        ) == type_mapping.get(new_cast_type, new_cast_type)
7426        if expr.is_type(data_type) or types_are_equivalent:
7427            return expr
7428
7429    expr = Cast(this=expr, to=data_type)
7430    expr.type = data_type
7431
7432    return expr
7433
7434
7435def table_(
7436    table: Identifier | str,
7437    db: t.Optional[Identifier | str] = None,
7438    catalog: t.Optional[Identifier | str] = None,
7439    quoted: t.Optional[bool] = None,
7440    alias: t.Optional[Identifier | str] = None,
7441) -> Table:
7442    """Build a Table.
7443
7444    Args:
7445        table: Table name.
7446        db: Database name.
7447        catalog: Catalog name.
7448        quote: Whether to force quotes on the table's identifiers.
7449        alias: Table's alias.
7450
7451    Returns:
7452        The new Table instance.
7453    """
7454    return Table(
7455        this=to_identifier(table, quoted=quoted) if table else None,
7456        db=to_identifier(db, quoted=quoted) if db else None,
7457        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
7458        alias=TableAlias(this=to_identifier(alias)) if alias else None,
7459    )
7460
7461
7462def values(
7463    values: t.Iterable[t.Tuple[t.Any, ...]],
7464    alias: t.Optional[str] = None,
7465    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
7466) -> Values:
7467    """Build VALUES statement.
7468
7469    Example:
7470        >>> values([(1, '2')]).sql()
7471        "VALUES (1, '2')"
7472
7473    Args:
7474        values: values statements that will be converted to SQL
7475        alias: optional alias
7476        columns: Optional list of ordered column names or ordered dictionary of column names to types.
7477         If either are provided then an alias is also required.
7478
7479    Returns:
7480        Values: the Values expression object
7481    """
7482    if columns and not alias:
7483        raise ValueError("Alias is required when providing columns")
7484
7485    return Values(
7486        expressions=[convert(tup) for tup in values],
7487        alias=(
7488            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
7489            if columns
7490            else (TableAlias(this=to_identifier(alias)) if alias else None)
7491        ),
7492    )
7493
7494
7495def var(name: t.Optional[ExpOrStr]) -> Var:
7496    """Build a SQL variable.
7497
7498    Example:
7499        >>> repr(var('x'))
7500        'Var(this=x)'
7501
7502        >>> repr(var(column('x', table='y')))
7503        'Var(this=x)'
7504
7505    Args:
7506        name: The name of the var or an expression who's name will become the var.
7507
7508    Returns:
7509        The new variable node.
7510    """
7511    if not name:
7512        raise ValueError("Cannot convert empty name into var.")
7513
7514    if isinstance(name, Expression):
7515        name = name.name
7516    return Var(this=name)
7517
7518
7519def rename_table(
7520    old_name: str | Table,
7521    new_name: str | Table,
7522    dialect: DialectType = None,
7523) -> Alter:
7524    """Build ALTER TABLE... RENAME... expression
7525
7526    Args:
7527        old_name: The old name of the table
7528        new_name: The new name of the table
7529        dialect: The dialect to parse the table.
7530
7531    Returns:
7532        Alter table expression
7533    """
7534    old_table = to_table(old_name, dialect=dialect)
7535    new_table = to_table(new_name, dialect=dialect)
7536    return Alter(
7537        this=old_table,
7538        kind="TABLE",
7539        actions=[
7540            RenameTable(this=new_table),
7541        ],
7542    )
7543
7544
7545def rename_column(
7546    table_name: str | Table,
7547    old_column_name: str | Column,
7548    new_column_name: str | Column,
7549    exists: t.Optional[bool] = None,
7550    dialect: DialectType = None,
7551) -> Alter:
7552    """Build ALTER TABLE... RENAME COLUMN... expression
7553
7554    Args:
7555        table_name: Name of the table
7556        old_column: The old name of the column
7557        new_column: The new name of the column
7558        exists: Whether to add the `IF EXISTS` clause
7559        dialect: The dialect to parse the table/column.
7560
7561    Returns:
7562        Alter table expression
7563    """
7564    table = to_table(table_name, dialect=dialect)
7565    old_column = to_column(old_column_name, dialect=dialect)
7566    new_column = to_column(new_column_name, dialect=dialect)
7567    return Alter(
7568        this=table,
7569        kind="TABLE",
7570        actions=[
7571            RenameColumn(this=old_column, to=new_column, exists=exists),
7572        ],
7573    )
7574
7575
7576def convert(value: t.Any, copy: bool = False) -> Expression:
7577    """Convert a python value into an expression object.
7578
7579    Raises an error if a conversion is not possible.
7580
7581    Args:
7582        value: A python object.
7583        copy: Whether to copy `value` (only applies to Expressions and collections).
7584
7585    Returns:
7586        The equivalent expression object.
7587    """
7588    if isinstance(value, Expression):
7589        return maybe_copy(value, copy)
7590    if isinstance(value, str):
7591        return Literal.string(value)
7592    if isinstance(value, bool):
7593        return Boolean(this=value)
7594    if value is None or (isinstance(value, float) and math.isnan(value)):
7595        return null()
7596    if isinstance(value, numbers.Number):
7597        return Literal.number(value)
7598    if isinstance(value, bytes):
7599        return HexString(this=value.hex())
7600    if isinstance(value, datetime.datetime):
7601        datetime_literal = Literal.string(value.isoformat(sep=" "))
7602
7603        tz = None
7604        if value.tzinfo:
7605            # this works for zoneinfo.ZoneInfo, pytz.timezone and datetime.datetime.utc to return IANA timezone names like "America/Los_Angeles"
7606            # instead of abbreviations like "PDT". This is for consistency with other timezone handling functions in SQLGlot
7607            tz = Literal.string(str(value.tzinfo))
7608
7609        return TimeStrToTime(this=datetime_literal, zone=tz)
7610    if isinstance(value, datetime.date):
7611        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
7612        return DateStrToDate(this=date_literal)
7613    if isinstance(value, tuple):
7614        if hasattr(value, "_fields"):
7615            return Struct(
7616                expressions=[
7617                    PropertyEQ(
7618                        this=to_identifier(k), expression=convert(getattr(value, k), copy=copy)
7619                    )
7620                    for k in value._fields
7621                ]
7622            )
7623        return Tuple(expressions=[convert(v, copy=copy) for v in value])
7624    if isinstance(value, list):
7625        return Array(expressions=[convert(v, copy=copy) for v in value])
7626    if isinstance(value, dict):
7627        return Map(
7628            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
7629            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
7630        )
7631    if hasattr(value, "__dict__"):
7632        return Struct(
7633            expressions=[
7634                PropertyEQ(this=to_identifier(k), expression=convert(v, copy=copy))
7635                for k, v in value.__dict__.items()
7636            ]
7637        )
7638    raise ValueError(f"Cannot convert {value}")
7639
7640
7641def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
7642    """
7643    Replace children of an expression with the result of a lambda fun(child) -> exp.
7644    """
7645    for k, v in tuple(expression.args.items()):
7646        is_list_arg = type(v) is list
7647
7648        child_nodes = v if is_list_arg else [v]
7649        new_child_nodes = []
7650
7651        for cn in child_nodes:
7652            if isinstance(cn, Expression):
7653                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
7654                    new_child_nodes.append(child_node)
7655            else:
7656                new_child_nodes.append(cn)
7657
7658        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
7659
7660
7661def replace_tree(
7662    expression: Expression,
7663    fun: t.Callable,
7664    prune: t.Optional[t.Callable[[Expression], bool]] = None,
7665) -> Expression:
7666    """
7667    Replace an entire tree with the result of function calls on each node.
7668
7669    This will be traversed in reverse dfs, so leaves first.
7670    If new nodes are created as a result of function calls, they will also be traversed.
7671    """
7672    stack = list(expression.dfs(prune=prune))
7673
7674    while stack:
7675        node = stack.pop()
7676        new_node = fun(node)
7677
7678        if new_node is not node:
7679            node.replace(new_node)
7680
7681            if isinstance(new_node, Expression):
7682                stack.append(new_node)
7683
7684    return new_node
7685
7686
7687def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7688    """
7689    Return all table names referenced through columns in an expression.
7690
7691    Example:
7692        >>> import sqlglot
7693        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7694        ['a', 'c']
7695
7696    Args:
7697        expression: expression to find table names.
7698        exclude: a table name to exclude
7699
7700    Returns:
7701        A list of unique names.
7702    """
7703    return {
7704        table
7705        for table in (column.table for column in expression.find_all(Column))
7706        if table and table != exclude
7707    }
7708
7709
7710def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7711    """Get the full name of a table as a string.
7712
7713    Args:
7714        table: Table expression node or string.
7715        dialect: The dialect to generate the table name for.
7716        identify: Determines when an identifier should be quoted. Possible values are:
7717            False (default): Never quote, except in cases where it's mandatory by the dialect.
7718            True: Always quote.
7719
7720    Examples:
7721        >>> from sqlglot import exp, parse_one
7722        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7723        'a.b.c'
7724
7725    Returns:
7726        The table name.
7727    """
7728
7729    table = maybe_parse(table, into=Table, dialect=dialect)
7730
7731    if not table:
7732        raise ValueError(f"Cannot parse {table}")
7733
7734    return ".".join(
7735        (
7736            part.sql(dialect=dialect, identify=True, copy=False)
7737            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7738            else part.name
7739        )
7740        for part in table.parts
7741    )
7742
7743
7744def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7745    """Returns a case normalized table name without quotes.
7746
7747    Args:
7748        table: the table to normalize
7749        dialect: the dialect to use for normalization rules
7750        copy: whether to copy the expression.
7751
7752    Examples:
7753        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7754        'A-B.c'
7755    """
7756    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7757
7758    return ".".join(
7759        p.name
7760        for p in normalize_identifiers(
7761            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7762        ).parts
7763    )
7764
7765
7766def replace_tables(
7767    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7768) -> E:
7769    """Replace all tables in expression according to the mapping.
7770
7771    Args:
7772        expression: expression node to be transformed and replaced.
7773        mapping: mapping of table names.
7774        dialect: the dialect of the mapping table
7775        copy: whether to copy the expression.
7776
7777    Examples:
7778        >>> from sqlglot import exp, parse_one
7779        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7780        'SELECT * FROM c /* a.b */'
7781
7782    Returns:
7783        The mapped expression.
7784    """
7785
7786    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7787
7788    def _replace_tables(node: Expression) -> Expression:
7789        if isinstance(node, Table):
7790            original = normalize_table_name(node, dialect=dialect)
7791            new_name = mapping.get(original)
7792
7793            if new_name:
7794                table = to_table(
7795                    new_name,
7796                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7797                    dialect=dialect,
7798                )
7799                table.add_comments([original])
7800                return table
7801        return node
7802
7803    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7804
7805
7806def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7807    """Replace placeholders in an expression.
7808
7809    Args:
7810        expression: expression node to be transformed and replaced.
7811        args: positional names that will substitute unnamed placeholders in the given order.
7812        kwargs: keyword arguments that will substitute named placeholders.
7813
7814    Examples:
7815        >>> from sqlglot import exp, parse_one
7816        >>> replace_placeholders(
7817        ...     parse_one("select * from :tbl where ? = ?"),
7818        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7819        ... ).sql()
7820        "SELECT * FROM foo WHERE str_col = 'b'"
7821
7822    Returns:
7823        The mapped expression.
7824    """
7825
7826    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7827        if isinstance(node, Placeholder):
7828            if node.this:
7829                new_name = kwargs.get(node.this)
7830                if new_name is not None:
7831                    return convert(new_name)
7832            else:
7833                try:
7834                    return convert(next(args))
7835                except StopIteration:
7836                    pass
7837        return node
7838
7839    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7840
7841
7842def expand(
7843    expression: Expression,
7844    sources: t.Dict[str, Query],
7845    dialect: DialectType = None,
7846    copy: bool = True,
7847) -> Expression:
7848    """Transforms an expression by expanding all referenced sources into subqueries.
7849
7850    Examples:
7851        >>> from sqlglot import parse_one
7852        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7853        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7854
7855        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7856        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7857
7858    Args:
7859        expression: The expression to expand.
7860        sources: A dictionary of name to Queries.
7861        dialect: The dialect of the sources dict.
7862        copy: Whether to copy the expression during transformation. Defaults to True.
7863
7864    Returns:
7865        The transformed expression.
7866    """
7867    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7868
7869    def _expand(node: Expression):
7870        if isinstance(node, Table):
7871            name = normalize_table_name(node, dialect=dialect)
7872            source = sources.get(name)
7873            if source:
7874                subquery = source.subquery(node.alias or name)
7875                subquery.comments = [f"source: {name}"]
7876                return subquery.transform(_expand, copy=False)
7877        return node
7878
7879    return expression.transform(_expand, copy=copy)
7880
7881
7882def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7883    """
7884    Returns a Func expression.
7885
7886    Examples:
7887        >>> func("abs", 5).sql()
7888        'ABS(5)'
7889
7890        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7891        'CAST(5 AS DOUBLE)'
7892
7893    Args:
7894        name: the name of the function to build.
7895        args: the args used to instantiate the function of interest.
7896        copy: whether to copy the argument expressions.
7897        dialect: the source dialect.
7898        kwargs: the kwargs used to instantiate the function of interest.
7899
7900    Note:
7901        The arguments `args` and `kwargs` are mutually exclusive.
7902
7903    Returns:
7904        An instance of the function of interest, or an anonymous function, if `name` doesn't
7905        correspond to an existing `sqlglot.expressions.Func` class.
7906    """
7907    if args and kwargs:
7908        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7909
7910    from sqlglot.dialects.dialect import Dialect
7911
7912    dialect = Dialect.get_or_raise(dialect)
7913
7914    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7915    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7916
7917    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7918    if constructor:
7919        if converted:
7920            if "dialect" in constructor.__code__.co_varnames:
7921                function = constructor(converted, dialect=dialect)
7922            else:
7923                function = constructor(converted)
7924        elif constructor.__name__ == "from_arg_list":
7925            function = constructor.__self__(**kwargs)  # type: ignore
7926        else:
7927            constructor = FUNCTION_BY_NAME.get(name.upper())
7928            if constructor:
7929                function = constructor(**kwargs)
7930            else:
7931                raise ValueError(
7932                    f"Unable to convert '{name}' into a Func. Either manually construct "
7933                    "the Func expression of interest or parse the function call."
7934                )
7935    else:
7936        kwargs = kwargs or {"expressions": converted}
7937        function = Anonymous(this=name, **kwargs)
7938
7939    for error_message in function.error_messages(converted):
7940        raise ValueError(error_message)
7941
7942    return function
7943
7944
7945def case(
7946    expression: t.Optional[ExpOrStr] = None,
7947    **opts,
7948) -> Case:
7949    """
7950    Initialize a CASE statement.
7951
7952    Example:
7953        case().when("a = 1", "foo").else_("bar")
7954
7955    Args:
7956        expression: Optionally, the input expression (not all dialects support this)
7957        **opts: Extra keyword arguments for parsing `expression`
7958    """
7959    if expression is not None:
7960        this = maybe_parse(expression, **opts)
7961    else:
7962        this = None
7963    return Case(this=this, ifs=[])
7964
7965
7966def array(
7967    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7968) -> Array:
7969    """
7970    Returns an array.
7971
7972    Examples:
7973        >>> array(1, 'x').sql()
7974        'ARRAY(1, x)'
7975
7976    Args:
7977        expressions: the expressions to add to the array.
7978        copy: whether to copy the argument expressions.
7979        dialect: the source dialect.
7980        kwargs: the kwargs used to instantiate the function of interest.
7981
7982    Returns:
7983        An array expression.
7984    """
7985    return Array(
7986        expressions=[
7987            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7988            for expression in expressions
7989        ]
7990    )
7991
7992
7993def tuple_(
7994    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7995) -> Tuple:
7996    """
7997    Returns an tuple.
7998
7999    Examples:
8000        >>> tuple_(1, 'x').sql()
8001        '(1, x)'
8002
8003    Args:
8004        expressions: the expressions to add to the tuple.
8005        copy: whether to copy the argument expressions.
8006        dialect: the source dialect.
8007        kwargs: the kwargs used to instantiate the function of interest.
8008
8009    Returns:
8010        A tuple expression.
8011    """
8012    return Tuple(
8013        expressions=[
8014            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
8015            for expression in expressions
8016        ]
8017    )
8018
8019
8020def true() -> Boolean:
8021    """
8022    Returns a true Boolean expression.
8023    """
8024    return Boolean(this=True)
8025
8026
8027def false() -> Boolean:
8028    """
8029    Returns a false Boolean expression.
8030    """
8031    return Boolean(this=False)
8032
8033
8034def null() -> Null:
8035    """
8036    Returns a Null expression.
8037    """
8038    return Null()
8039
8040
8041NONNULL_CONSTANTS = (
8042    Literal,
8043    Boolean,
8044)
8045
8046CONSTANTS = (
8047    Literal,
8048    Boolean,
8049    Null,
8050)